SQL GROUP BY Interview Questions

GROUP BY questions test whether you can turn rows into a business summary without losing the meaning of each row. The main decision is the output grain: one row per category, customer, month, channel, movie, or supplier.

Once you know the grain, a safe portable rule is that each selected non-aggregated column should be part of the grouping key, while measures are wrapped in aggregates such as count, sum, avg, min, or max. Some engines can infer functional dependencies in specific cases, but explicit grouping is clearer in an interview.

Common GROUP BY interview prompts

Answer each one yourself before you look: write your query or your reasoning in the box, or open the playground and run it. Then reveal the model answer and compare. Nothing you type is saved or sent anywhere.

1. Count products by category and include average price and total units on hand.

   Your answer Try it in the playground →
   Reveal a model answer

   select c.category_name,
          count(*) as products,
          round(avg(p.price), 2) as avg_price,
          sum(p.quantity_on_hand) as units_on_hand
   from products as p
   join categories as c on c.category_id = p.category_id
   group by c.category_name
   order by products desc, c.category_name

   One row per category, so category_name is the only plain column and everything else is an aggregate. Round the average for display, not before summing anything.

2. Return customers with at least $75 in shipped revenue.

   Your answer Try it in the playground →
   Reveal a model answer

   select c.customer_id,
          c.first_name,
          c.last_name,
          sum(oi.quantity * oi.unit_price) as revenue
   from customers as c
   join orders as o on o.customer_id = c.customer_id
   join order_items as oi on oi.order_id = o.order_id
   where o.status = 'shipped'
   group by c.customer_id, c.first_name, c.last_name
   having sum(oi.quantity * oi.unit_price) >= 75
   order by revenue desc

   Two filters, two clauses: shipped is a fact about each row, so WHERE; at least $75 is a fact about each customer's total, so HAVING.

3. Find the top five pages by pageviews.

   Your answer Try it in the playground →
   Reveal a model answer

   select path, count(*) as pageviews
   from pageviews
   group by path
   order by pageviews desc, path
   limit 5

   Several paths tie on views, so the tie-breaker decides who makes the cut. Ask whether ties at the boundary should all be included; if so, this becomes a rank() question.

4. Calculate sessions and average duration by marketing channel.

   Your answer Try it in the playground →
   Reveal a model answer

   select channel,
          count(*) as sessions,
          round(avg(duration_seconds), 1) as avg_seconds
   from sessions
   group by channel
   order by sessions desc, channel

   Volume and engagement in one grouped pass. Mention that an average hides spread: one very long session can lift a small channel.

5. Explain the difference between WHERE and HAVING.

   Your answer
   Reveal a model answer

   WHERE filters rows before grouping, so it cannot see aggregates. HAVING filters groups after aggregation, so it can. Put a condition in WHERE whenever it can be checked on a single row: it is clearer, and it shrinks the data before the grouping work.

select c.category_name,
       count(*) as products,
       round(avg(p.price), 2) as avg_price,
       sum(p.quantity_on_hand) as units_on_hand
from products as p
join categories as c on c.category_id = p.category_id
group by c.category_name
having count(*) >= 2
order by products desc, c.category_name

What matters

A product-by-category report groups on category_name and then aggregates the product rows inside each category. If you add product_name to the SELECT list, the query no longer has one row per category unless you also group by product name.

The HAVING clause filters groups after aggregation. Use WHERE for row-level filters such as shipped orders or a date range; use HAVING for aggregate filters such as customers with revenue above a threshold.

select c.customer_id,
       c.first_name,
       c.last_name,
       round(sum(oi.quantity * oi.unit_price), 2) as revenue
from customers as c
join orders as o on o.customer_id = c.customer_id
join order_items as oi on oi.order_id = o.order_id
where o.status = 'shipped'
group by c.customer_id, c.first_name, c.last_name
having sum(oi.quantity * oi.unit_price) >= 75
order by revenue desc

How to talk it through

Start with the shape: "This report is one row per customer, so I will group by the customer identifier and display columns. I will filter to shipped orders in WHERE before calculating revenue, then use HAVING to keep only customers whose aggregate revenue meets the threshold."

If the interviewer asks for a percentage or rate, call out integer division. Use 100.0 or an explicit decimal cast when the engine would otherwise truncate integer division.

Practice next

Review GROUP BY, HAVING, and the GROUP BY error guide. Then try the revenue audit mission and movie genre scorecard.