SQL for Analytics - lesson 2 | HackIndia Skills

SQL for Analytics - lesson 2 | HackIndia Skills | HackIndia

Example

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);

Example

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;

Example

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;

Tip

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';

Summarise with GROUP BY

One idea

Why it matters

Sample events

Build a summary

Daily signups

Revenue by city

Common mistake

Check with totals

Do this now

Quick check