Playlist Genre Coverage
Identify playlists with the broadest genre diversity for the "Discover" carousel. Schema: Playlist ColumnType...
JOINMultiple TablesCOUNT DISTINCTGROUP BY+1
Intermediate
10m25 pts
2100%
SQL Practice Hub
SQL Aggregation and GROUP BY Practice Questions
Collapse many rows into one summary row per group.
61 questionsFreeRuns in your browser
- GROUP BY
- Names the columns that define each group.
- SUM, COUNT, AVG
- Describe each group with one number (also MIN and MAX).
- HAVING
- Filters groups after aggregation; WHERE cannot use aggregates.
What you will practice
- Know when a column must appear in GROUP BY and why the database rejects it otherwise
- Use WHERE to filter rows before grouping and HAVING to filter groups afterwards
- Understand how COUNT(*), COUNT(column) and COUNT(DISTINCT column) differ on NULLs and duplicates
- Write conditional aggregation with SUM(CASE WHEN ... THEN 1 ELSE 0 END) to pivot a column into metrics
- Combine aggregates with joins without double-counting the fact table
Read the full overview
Aggregation collapses many rows into one summary row per group: GROUP BY names the grouping columns, and SUM, COUNT, AVG, MIN and MAX describe each group. HAVING then filters the groups themselves, which is why it can reference an aggregate while WHERE cannot. This is the largest topic in the question bank because almost every analyst task, from revenue by month to active users per plan, is an aggregation with the right grouping key.
61 questionsPage 4 of 5
Quarterly Genre Sales (2012-2013)
Show quarter-by-quarter revenue per genre across calendar years 2012 and 2013 for the four largest genres. Schema:...
EXTRACTQUARTERJOINMultiple Tables+3Intermediate12m30 pts1100%First-Purchase Cohort Count by Month
Show how many customers made their first-ever purchase each month — the new-customer cohort series. Schema: Invoice...
SubqueryGROUP BYMINTO_CHAR+3Intermediate10m25 pts267%YoY Genre Revenue (Stable Cohort)
For genres present in every calendar year of available data, compute year-over-year revenue growth. Schema: InvoiceLine...
CTEMultiple CTEsWindow FunctionsLAG+2Advanced15m40 pts7100%Shortest Inter-Purchase Gap per Customer
For each customer, compute their shortest gap (in days) between any two consecutive purchases. This surfaces the most...
CTEWindow FunctionsLAGDate Arithmetic+3Advanced15m40 pts7100%Manager Subtree Sales (Recursive)
For each manager, compute the total sales their entire organizational subtree generated (the manager's direct reports,...
Recursive CTEEmployee HierarchySUMJOIN+1Advanced18m45 pts8100%Day-of-Week Revenue Pivot
Pivot revenue per billing country across days of the week — useful for spotting weekend-heavy markets. Schema: Invoice...
CTEEXTRACTDOWConditional Aggregation+5Advanced15m40 pts8100%Customers Above a Spending Threshold (CTE)
Using a CTE, first compute each customer's total spending (sum of their invoice totals). Then, from that CTE, return...
CTEaggregationJOINIntermediate7m25 pts1100%Standard vs. Premium Priced Tracks by Genre (Pivot)
Pricing wants a per-genre breakdown of how many tracks are sold at the standard $0.99 rate versus the premium rate...
PivotConditional AggregationFlipkartIntermediate7m25 pts1100%Albums Above the Average Track Count (CTE)
Using a CTE, compute each album's track count, then return only albums whose track count exceeds the average track...
CTEaggregationIntermediate6m25 pts1100%Rank Artists by Total Track Count (RANK)
Compute each artist's total track count (across all their albums) and rank artists by that count, highest first, using...
Window FunctionsRANKJOINaggregation+1Intermediate6m25 pts1100%Dense Rank Customers by Invoice Count (DENSE_RANK)
Compute each customer's total number of invoices, then rank customers by that count (most invoices first) using...
Window FunctionsDENSE_RANKGROUP BYIntermediate6m25 pts1100%Cumulative Genre Revenue, Highest First (Running Total)
Ops wants a Pareto-style view of genre revenue: compute each genre's total revenue, then -- ordered from...
Window FunctionsRunning TotalSUMAmazonIntermediate7m25 pts1100%Genre Track Counts by Media Type (Pivot)
The catalog team wants a per-genre breakdown of tracks across the 3 most common media types, using conditional...
PivotConditional AggregationIntermediate7m25 pts1100%W8: Monthly MRR Rollup
Pre-aggregated mart: total MRR per plan per month of start_date. Return month, plan, total_mrr. Order by month, plan.
data-engineerw8warehousingaggregationIntermediate5m10 pts0—
FAQ
SQL Aggregation and GROUP BY practice: common questions
Short answers to what people get stuck on most. Tap a question to expand it.
What is the difference between WHERE and HAVING?
WHERE filters rows before grouping. HAVING filters groups after aggregation.
- WHERE cannot use aggregates like SUM or COUNT; HAVING can.
- WHERE also shrinks the data before GROUP BY runs, so it is cheaper.
- Rule of thumb: put every condition you can in WHERE, and keep HAVING for aggregate conditions.
SQL
SELECT region, SUM(amount) AS total
FROM orders
WHERE status = 'paid' -- row filter, before grouping
GROUP BY region
HAVING SUM(amount) > 1000; -- group filter, after groupingWhat is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?
They count different things: all rows, non-NULL values, or unique non-NULL values.
- COUNT(*): every row in the group, NULLs included.
- COUNT(column): only rows where that column is not NULL.
- COUNT(DISTINCT column): unique non-NULL values, what you want for how many customers after a join repeats them.
- Mixing them up is a common cause of inflated or understated metrics.
SQL
SELECT COUNT(*) AS order_rows,
COUNT(coupon_code) AS orders_with_coupon,
COUNT(DISTINCT user_id) AS unique_customers
FROM orders;Why do I get an error when I select a column that is not in GROUP BY?
Every selected column must be in GROUP BY or inside an aggregate, because the database cannot pick one value for the whole group.
- Add the column to GROUP BY if you want one row per combination (shown here).
- Wrap it in MIN or MAX if any value in the group will do.
- Use a window function if you need every row next to the aggregate.
- MySQL with ONLY_FULL_GROUP_BY off silently picks a random value, which hides the bug.
SQL
-- error: name is neither grouped nor aggregated
SELECT customer_id, name, SUM(amount) FROM sales GROUP BY customer_id;
-- fix: group by it as well
SELECT customer_id, name, SUM(amount) FROM sales GROUP BY customer_id, name;Related practice topics
Track your streak & earn XP
Sign up free to unlock your progress dashboard, daily streaks, and leaderboard ranking.
Full-Length Interview Practice Tests100% Free
10 role-based tests for Data Analysts, SQL Developers & Data Engineers with instant scorecards.