One idea
GROUP BY makes one output row for each group you choose. Every selected column must either be grouped or aggregated.
First decide the grain: one row per day, user, plan, city, campaign, or product. Then choose the metric: count, sum, average, min, or max.
Why it matters
Product event tables are long. One user may create many events: signup, search, add_to_cart, payment_success, and more.
A founder or PM does not want raw rows. They ask: how many signups happened each day, which plan earns more, or which city has higher orders. GROUP BY is how you answer that.
Sample events
Use a small table like this in your project. It is enough to practise daily activity, revenue, and product usage questions.
-- events table
-- event_id, user_id, event_name, event_time, city, plan, amount
INSERT INTO events VALUES
(1, 'u1', 'signup', '2026-01-10 09:10:00', 'Pune', 'free', 0),
(2, 'u1', 'payment_success', '2026-01-12 11:00:00', 'Pune', 'pro', 499),
(3, 'u2', 'signup', '2026-01-10 10:30:00', 'Delhi', 'free', 0),
(4, 'u3', 'signup', '2026-01-11 14:20:00', 'Bengaluru', 'free', 0),
(5, 'u3', 'payment_success', '2026-01-13 16:45:00', 'Bengaluru', 'pro', 499);Build a summary
Do each one yourself, then tap it to tick it off. The ticks are only a checklist for you: they are not marked or scored.
0 of 5 done
Daily signups
Question: how many users signed up each day? The grain is one row per signup date.
SELECT
DATE(event_time) AS signup_date,
COUNT(*) AS signup_events,
COUNT(DISTINCT user_id) AS signup_users
FROM events
WHERE event_name = 'signup'
GROUP BY DATE(event_time)
ORDER BY signup_date;Revenue by city
Question: which cities brought payment revenue? The grain is one row per city.
For money, use SUM. COUNT payments separately so you can see if revenue comes from one payment or many.
SELECT
city,
COUNT(*) AS payments,
SUM(amount) AS revenue_rs,
AVG(amount) AS avg_payment_rs
FROM events
WHERE event_name = 'payment_success'
GROUP BY city
ORDER BY revenue_rs DESC;Common mistake
Do not select random columns that are not in GROUP BY. If you group by city but also select user_id, the result no longer has one clear row per city.
Also check COUNT(*) versus COUNT(DISTINCT user_id). COUNT(*) counts rows. COUNT(DISTINCT user_id) counts people. Product questions often need people.
Check with totals
After a summary query, run a quick total query on the same filter. The sum of grouped counts should match the total count.
This catches missing filters, duplicate events, and wrong grain before you add the query to your HackIndia submission.
-- Total payments should match the sum of payments by city
SELECT
COUNT(*) AS total_payments,
SUM(amount) AS total_revenue_rs
FROM events
WHERE event_name = 'payment_success';Do this now
Do each one yourself, then tap it to tick it off. The ticks are only a checklist for you: they are not marked or scored.
0 of 5 done