Skip to main content

LeetCode 1126. Active Businesses SQL Solution

Problem​

LeetCode SQL Problem

  1. Active Businesses

Events table

business_idevent_typeoccurences
1reviews7
3reviews3
1ads11
2ads7
3ads6
1page views3
2page views12

Solution - Group By and CTEs​

Active Businesses
WITH AvgEventOccurences
AS (
	SELECT event_type
		-- Calculate the average occurences of each event type among all businesses
		,avg(cast(occurences AS FLOAT)) AS avg_occurences
	FROM Events
	GROUP BY event_type
	)
SELECT E.business_id
FROM Events AS E
INNER JOIN AvgEventOccurences AS AG ON E.event_type = AG.event_type
-- Occurrences of current event type is greater than the average occurences of this event type among all businesses.
WHERE E.occurences > AG.avg_occurences
GROUP BY E.business_id
-- If a business has more than 1 event type that matches the criteria, it's considered Active
HAVING count(DISTINCT E.event_type) > 1

Solution - AVG() Window Function​

Active Businesses
WITH EventsAnalysis
AS (
	SELECT *
		-- Calculate the average occurences of this row's event type among all businesses
		,avg(cast(occurences AS FLOAT)) OVER (PARTITION BY event_type) AS avg_occurences
	FROM Events
	)
SELECT business_id
FROM EventsAnalysis
-- Occurrences of current event type is greater than the average occurences of this event type among all businesses.
WHERE occurences > avg_occurences
GROUP BY business_id
-- If a business has more than 1 event type that matches the criteria, it's considered Active
HAVING count(DISTINCT event_type) > 1

Query Output

business_id
1