All the reading

SQL for Analytics

8 lessons, about 36 minutes. The check's questions come from these.

Rewriting product questions

1. Ask better data questions

4 min

Key point

One idea

Good SQL starts before SQL. First write the product question so clearly that another person can build the same query.

A clear question names the metric, who it is about, the event or table, the time period, and how the result should be grouped.

Why this matters

Most bad analytics projects do not fail because of syntax. They fail because the question is vague.

“How is our app doing?” can mean signups, payments, retention, active users, or revenue. Each needs a different query and a different answer.

Your to-do

Question checklist

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.

Name the decision the answer will help with.
Choose one metric, like signups, orders, revenue, or completion rate.
Define the users or rows included in the answer.
Set a time range, like last 7 days or January 2026.
Choose the breakdown, like day, city, plan, device, or college.

0 of 5 done

Example

Make it queryable

Here is a weak question and a better version for a campus food ordering app.

The better version is ready for SQL because it tells you what to count and how to group it.

Weak: Are users ordering food?

Better: For users in Bengaluru, how many paid orders were placed each day from 1 Jan 2026 to 7 Jan 2026?

Metric: paid orders
Rows included: orders where status = 'paid'
Time range: 2026-01-01 to 2026-01-07
Group by: order date
Filter: city = 'Bengaluru'

4. Build readable queries with CTEs

4 min

Key point

One idea

A CTE is a named temporary result inside one query. Use it to make each step of your logic visible.

Your final SELECT should read like the answer. The CTEs above it should show how you got there.

Why CTEs matter

In product analytics, one question often needs many steps. For example: filter valid events, join users, calculate revenue, rank cities.

You can do this with nested subqueries. But they become hard to check. CTEs make the same work easier to read, debug, and explain in your project README.

Your to-do

CTE query shape

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.

Start with WITH before your main SELECT.
Name each CTE after the result it creates, not the method.
Keep one clear job per CTE, like paid_orders or city_revenue.
Select from the last CTE in your final query.
Run each CTE as a separate SELECT while checking your logic.

0 of 5 done

8. Submit your SQL analytics project

5 min

Key point

The proof matters

Your project is not just a set of smart queries. It is a small evidence pack.

A reviewer should be able to open your repo, load the sample data, run your SQL, compare outputs, and understand what the numbers mean.

What to submit

Use one public GitHub repository. Keep it small and easy to inspect on a phone or laptop.

Your repo should show the full path from product question to SQL answer. Do not hide the data checks or only show final screenshots.

Your to-do

Project checklist

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.

Add a README with the product context and 3 to 5 questions you answered.
Add sample data as CSV files or a SQL seed file.
Add separate SQL files for cohorts, funnels, rankings, and final metrics.
Add checked outputs as CSV, text, or screenshots from your SQL tool.
Add a short insights section that explains what changed, grew, dropped, or needs action.

0 of 5 done

Counting and grouping events

2. Summarise with GROUP BY

4 min

Key point

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.

Example

Sample events

Use a small table like this in your project. It is enough to practise daily activity, revenue, and product usage questions.

Ranking and checking results

3. Rank and total with windows

5 min

Key point

One idea

Window functions calculate across a group of rows, but they do not collapse rows like GROUP BY does.

Use them when you want each row to stay visible, with an extra rank, running total, or share added beside it.

Why windows matter

Product questions often need both detail and comparison. Example: which feature got the most events this week, and what share of total activity did each feature get?

GROUP BY can give one row per feature. A window can then rank those rows and add totals without writing a separate query for every metric.

Example

Sample data

Use a small events table like this in your project. It is enough to practise rankings, totals, and shares.

10 questions, 7 to pass.

Example

Start a question log

Your final project will need a short explanation of insights. A question log makes that easier.

Create a plain file in your project folder. Add one row for every question before you write the query.

File: questions.md

Question 1
Product question: Are paid orders increasing week by week?
SQL-ready question: Count paid orders by week for Jan 2026.
Metric: paid orders
Table needed: orders
Filters: status = 'paid'
Time range: 2026-01-01 to 2026-01-31
Group by: week
Why it matters: Shows whether demand is growing.
Output expected: week, paid_orders

Example

Worked example

Question: Which 5 cities brought the most paid order revenue?

This query uses CTEs to make the filter, join, aggregation, and ranking easy to follow.

WITH paid_orders AS (
  SELECT
    order_id,
    user_id,
    amount_rupees
  FROM orders
  WHERE status = 'paid'
),
orders_with_city AS (
  SELECT
    p.order_id,
    p.amount_rupees,
    u.city
  FROM paid_orders p
  JOIN users u
    ON p.user_id = u.user_id
),
city_revenue AS (
  SELECT
    city,
    SUM(amount_rupees) AS total_revenue_rupees,
    COUNT(*) AS paid_orders
  FROM orders_with_city
  GROUP BY city
),
ranked_cities AS (
  SELECT
    city,
    total_revenue_rupees,
    paid_orders,
    RANK() OVER (ORDER BY total_revenue_rupees DESC) AS revenue_rank
  FROM city_revenue
)
SELECT
  city,
  total_revenue_rupees,
  paid_orders,
  revenue_rank
FROM ranked_cities
WHERE revenue_rank <= 5
ORDER BY revenue_rank, city;

Example

Simple repo layout

This layout is enough for a hackathon submission. Change names to match your project, like food delivery, UPI expense tracking, edtech lessons, or campus events.

product-sql-analytics/
  README.md
  data/
    users.csv
    events.csv
    orders.csv
  queries/
    01_check_counts.sql
    02_weekly_active_users.sql
    03_signup_to_purchase_funnel.sql
    04_signup_cohorts.sql
    05_top_products.sql
  outputs/
    01_check_counts.csv
    02_weekly_active_users.csv
    03_signup_to_purchase_funnel.csv
    04_signup_cohorts.csv
    05_top_products.csv

Example

README skeleton

Write the README for a busy reviewer. They may spend only two minutes before deciding if your work is clear.

# Product SQL Analytics Project

## Context
This project analyses sample events for a campus food ordering app.
The goal is to understand activation, ordering, and repeat use.

## Questions answered
1. How many users are active each week?
2. What percent of signups place their first order?
3. Which signup cohorts return in later weeks?
4. Which items generate the most revenue?

## How to run
1. Create tables using the CSV files in /data.
2. Run queries in the order shown in /queries.
3. Compare your results with the files in /outputs.

## Checks done
- Checked row counts for each table.
- Checked date ranges before using them.
- Checked that funnel steps do not increase after each stage.
- Checked that revenue equals quantity * price.

## Key insights
- Weekly active users increased from 420 to 610 in the sample period.
- 38% of signups placed an order within 7 days.
- The August signup cohort had better week 2 retention than July.

## Next action
Improve first-order conversion by testing a small discount on day 1.

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

5. Handle dates correctly

4 min

Key point

One date rule

Keep the raw event timestamp as your source of truth. Create date, week, or month fields in the query when you need them.

Most wrong product numbers come from unclear time zones, half-open days, or grouping by the full timestamp instead of the date.

Know your columns

Product events usually have an event timestamp, like created_at or event_time. That is the exact moment an event happened.

For analytics, you often need a calendar day, week, or month. These are derived from the timestamp, not stored as a replacement.

Your to-do

Choose the grain

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.

Use day for daily active users, orders, signups, and streaks.
Use week for college timetable-friendly reports and trend checks.
Use month for revenue, retention summaries, and founder updates.
Name derived columns clearly, like event_date, event_week, or event_month.
Group by the derived date column, not the raw timestamp.

0 of 5 done

Common mistake

Bad date filters

Do not filter a full day with event_time = '2026-01-10'. A timestamp includes hours, minutes, and seconds, so this will miss most rows.

Use a start time and an end time. The end should be exclusive. This avoids bugs at 23:59:59 and works cleanly for weeks and months too.

Example

Daily signups

This query counts signups per day for January 2026. It keeps the filter on the timestamp, then groups by the date.

This style works in PostgreSQL. If your database has different date functions, keep the same idea.

SELECT
  DATE(created_at) AS signup_date,
  COUNT(*) AS signups
FROM users
WHERE created_at >= TIMESTAMP '2026-01-01'
  AND created_at <  TIMESTAMP '2026-02-01'
GROUP BY DATE(created_at)
ORDER BY signup_date;

Tip

Handle India time

If your app stores UTC timestamps, decide whether the product question needs UTC or India time. A UPI payments dashboard for Indian users usually needs India time.

Write the choice in your project explanation. For example: “All daily metrics use Asia/Kolkata calendar days.”

Example

Weekly orders

Weeks are useful when daily numbers are noisy. For hackathon projects, week-level charts are easier to explain than 30 small daily bars.

This query groups paid orders by week and checks revenue in rupees.

SELECT
  DATE_TRUNC('week', paid_at) AS paid_week,
  COUNT(*) AS paid_orders,
  SUM(amount_rupees) AS revenue_rupees
FROM orders
WHERE paid_at >= TIMESTAMP '2026-01-01'
  AND paid_at <  TIMESTAMP '2026-04-01'
GROUP BY DATE_TRUNC('week', paid_at)
ORDER BY paid_week;

Your to-do

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.

Open your sample events, users, or orders table.
Find every timestamp column and write what it means.
Write one query that groups by day.
Write one query that filters a full month using start-inclusive and end-exclusive dates.
Add one note to your project README about the time zone you use.

0 of 5 done

6. Write cohort and funnel queries

5 min

Key point

One idea

Cohorts and funnels both start with user-level milestones. First find each user’s key dates or steps, then count users from that clean table.

Do not count raw event rows first. One active student can create many events and make your product look better than it is.

Cohort meaning

A cohort is a group of users who started in the same period. For example, users who signed up in July, or users whose first order was in week 32.

A retention cohort asks: after they started, how many came back in a later period? This is useful for apps, clubs, SaaS tools, marketplaces, and campus products.

Your to-do

Cohort query shape

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.

Find each user’s first date, like first signup or first purchase.
Join later activity for the same users.
Convert both dates to periods, such as month or week.
Calculate the period number after start, such as month 0, month 1, month 2.
Count distinct users in each cohort and period.

0 of 5 done

Example

Monthly retention

This example uses an events table with user_id, event_name, and event_time. It groups users by signup month, then counts who was active in later months.

Change event names to match your sample data. If your database is SQLite, use strftime. If you use PostgreSQL, use date_trunc.

-- PostgreSQL
WITH signups AS (
  SELECT
    user_id,
    DATE_TRUNC('month', MIN(event_time)) AS cohort_month
  FROM events
  WHERE event_name = 'signup'
  GROUP BY user_id
),
activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', event_time) AS activity_month
  FROM events
  WHERE event_name IN ('login', 'view_product', 'place_order')
),
cohort_activity AS (
  SELECT
    s.cohort_month,
    a.activity_month,
    (
      EXTRACT(YEAR FROM a.activity_month) * 12 + EXTRACT(MONTH FROM a.activity_month)
    ) - (
      EXTRACT(YEAR FROM s.cohort_month) * 12 + EXTRACT(MONTH FROM s.cohort_month)
    ) AS month_number,
    s.user_id
  FROM signups s
  JOIN activity a ON a.user_id = s.user_id
  WHERE a.activity_month >= s.cohort_month
)
SELECT
  cohort_month,
  month_number,
  COUNT(DISTINCT user_id) AS retained_users
FROM cohort_activity
GROUP BY cohort_month, month_number
ORDER BY cohort_month, month_number;

Funnel meaning

A funnel measures how many users complete steps in order. Example: signup, view product, add to cart, place order.

For a hackathon analytics project, a funnel is stronger when it answers a real question. For example: where do users drop before paying by UPI?

Example

Ordered funnel

This query counts users who reached each step after the previous step. It prevents a user from being counted for checkout if checkout happened before signup.

Use MIN to keep the first time a user did each step.

WITH user_steps AS (
  SELECT
    user_id,
    MIN(CASE WHEN event_name = 'signup' THEN event_time END) AS signup_time,
    MIN(CASE WHEN event_name = 'view_product' THEN event_time END) AS view_time,
    MIN(CASE WHEN event_name = 'add_to_cart' THEN event_time END) AS cart_time,
    MIN(CASE WHEN event_name = 'place_order' THEN event_time END) AS order_time
  FROM events
  GROUP BY user_id
),
valid_steps AS (
  SELECT
    user_id,
    signup_time,
    CASE WHEN view_time >= signup_time THEN view_time END AS view_time,
    CASE WHEN cart_time >= view_time AND view_time >= signup_time THEN cart_time END AS cart_time,
    CASE WHEN order_time >= cart_time AND cart_time >= view_time AND view_time >= signup_time THEN order_time END AS order_time
  FROM user_steps
)
SELECT 'signup' AS step, COUNT(*) AS users FROM valid_steps WHERE signup_time IS NOT NULL
UNION ALL
SELECT 'view_product', COUNT(*) FROM valid_steps WHERE view_time IS NOT NULL
UNION ALL
SELECT 'add_to_cart', COUNT(*) FROM valid_steps WHERE cart_time IS NOT NULL
UNION ALL
SELECT 'place_order', COUNT(*) FROM valid_steps WHERE order_time IS NOT NULL;

Common mistake

Do not calculate funnel steps with total event counts. If one user views 20 products, that is still one user reaching the view step.

Also avoid mixing time windows. A weekly funnel should use events from the same defined window, or you should clearly say it is lifetime conversion.

Your to-do

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.

Pick one cohort question for your project, such as signup month retention.
Pick one funnel question, such as signup to first order.
Add two SQL files: cohort_retention.sql and funnel_conversion.sql.
Run both queries on your sample data and save the outputs.
Write two lines explaining what changed across cohorts or where the funnel drops.

0 of 5 done

create table events (
  event_id integer,
  user_id integer,
  event_name text,
  feature text,
  event_time timestamp,
  amount_rs integer
);

insert into events values
(1, 101, 'view',     'payments', '2026-01-10 09:00:00', 0),
(2, 102, 'view',     'chat',     '2026-01-10 09:05:00', 0),
(3, 101, 'pay',      'payments', '2026-01-10 09:10:00', 299),
(4, 103, 'view',     'payments', '2026-01-10 09:20:00', 0),
(5, 104, 'message',  'chat',     '2026-01-10 09:30:00', 0),
(6, 105, 'pay',      'payments', '2026-01-10 10:00:00', 499),
(7, 102, 'message',  'chat',     '2026-01-10 10:05:00', 0),
(8, 106, 'view',     'catalog',  '2026-01-10 10:20:00', 0);

Example

Rank features

This query answers: which features were used most on 10 Jan? It keeps one row per feature, then ranks the features by event count.

rank() can skip numbers after ties. dense_rank() does not skip. For a leaderboard, dense_rank() is often easier to read.

select
  feature,
  count(*) as event_count,
  dense_rank() over (order by count(*) desc) as feature_rank,
  round(
    100.0 * count(*) / sum(count(*)) over (),
    2
  ) as pct_of_events
from events
where event_time >= '2026-01-10'
  and event_time <  '2026-01-11'
group by feature
order by feature_rank, feature;

Example

Running revenue

This query answers: how much revenue had arrived after each payment? It shows the running total in rupees after every pay event.

This is useful for order timelines, daily sales, wallet top-ups, or UPI payment flows in a student startup project.

select
  event_time,
  user_id,
  amount_rs,
  sum(amount_rs) over (
    order by event_time
    rows between unbounded preceding and current row
  ) as running_revenue_rs
from events
where event_name = 'pay'
order by event_time;

7. Check numbers before presenting

5 min

Key point

Trust is earned

A metric is not ready when the query runs. It is ready when you have checked that the number makes sense from at least two angles.

Your project should show these checks. Recruiters and judges may not rerun everything, but they can see that you think like an analyst.

What to check

Check totals, date ranges, duplicates, nulls, and impossible values. Then compare your metric with a simpler query that should roughly agree.

For example, if your funnel says 120 users reached payment, the raw event count for payment_started should not be 20. If your college app has signups before launch date, something is wrong.

Your to-do

Basic check flow

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.

Count rows in each source table before analysis.
Check the minimum and maximum dates used in the query.
Look for duplicate event IDs or duplicate user-event timestamps.
Count nulls in important columns like user_id, event_name, amount, and created_at.
Recompute the final metric with a simpler query and compare.

0 of 5 done

Example

Checks query file

Add a file like checks.sql beside your main queries. Keep it boring and clear.

This example assumes a product_events table with event_id, user_id, event_name, created_at, and amount_inr.

-- checks.sql

-- 1. Row count and date range
SELECT
  COUNT(*) AS total_events,
  MIN(created_at) AS first_event_at,
  MAX(created_at) AS last_event_at
FROM product_events;

-- 2. Null checks for required fields
SELECT
  SUM(CASE WHEN event_id IS NULL THEN 1 ELSE 0 END) AS null_event_ids,
  SUM(CASE WHEN user_id IS NULL THEN 1 ELSE 0 END) AS null_user_ids,
  SUM(CASE WHEN event_name IS NULL THEN 1 ELSE 0 END) AS null_event_names,
  SUM(CASE WHEN created_at IS NULL THEN 1 ELSE 0 END) AS null_created_at
FROM product_events;

-- 3. Duplicate event IDs
SELECT
  event_id,
  COUNT(*) AS times_seen
FROM product_events
GROUP BY event_id
HAVING COUNT(*) > 1;

-- 4. Impossible payment values
SELECT
  COUNT(*) AS bad_payment_rows
FROM product_events
WHERE event_name = 'payment_success'
  AND (amount_inr IS NULL OR amount_inr <= 0);

-- 5. Simple payment user count for comparison
SELECT
  COUNT(DISTINCT user_id) AS users_with_successful_payment
FROM product_events
WHERE event_name = 'payment_success';

Common mistake

Do not hide mismatches

Beginners often change the query until the number looks nice. Do the opposite. Write down the mismatch and the likely reason.

A 2 percent difference may come from filters, date cutoffs, test users, or duplicate events. A 50 percent difference usually means the query logic is wrong.

Tip

Use AI carefully

You can ask ChatGPT, Gemini, or Claude to review your SQL checks. Paste your table columns, your metric query, and ask: “What sanity checks should I run before presenting this?”

Do not trust generated SQL blindly. Check table names, date functions, join keys, and whether the AI assumed a database you are not using.

Example

Write checked outputs

In your project, save results from checks in a small text file or README section. This proves you did not only write queries.

Keep it short. Mention what passed and what you fixed.

Checked outputs

Dataset: sample_product_events.csv
Rows: 2,450 events
Date range: 2024-07-01 to 2024-07-31
Duplicate event_id rows: 0
Null user_id rows: 0
Bad payment_success amount rows: 0

Metric checked:
Funnel query payment_success users = 186
Simple distinct payment_success users = 186
Status: matches

Note:
Removed 4 test users from all insight queries using user_id NOT LIKE 'test_%'.

Your to-do

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.

Create a checks.sql file in your analytics project folder.
Add row count, date range, null, duplicate, and impossible value checks.
Run the checks on your sample data and save the outputs.
Compare one main metric with a simpler query.
Write one note explaining any mismatch or cleanup.

0 of 5 done

SQL for Analytics

1. Ask better data questions

One idea

Why this matters

Question checklist

Make it queryable

4. Build readable queries with CTEs

One idea

Why CTEs matter

CTE query shape

8. Submit your SQL analytics project

The proof matters

What to submit

Project checklist

2. Summarise with GROUP BY

One idea

Why it matters

Sample events

3. Rank and total with windows

One idea

Why windows matter

Sample data

Use AI well

Avoid hidden definitions

Start a question log

Do this now

Worked example

Name for reviewers

Common mistake

Use AI carefully

Do this now

Simple repo layout

README skeleton

Do not submit magic

Use AI carefully

Do this now

Build a summary

Daily signups

Revenue by city

Common mistake

Check with totals

Do this now

5. Handle dates correctly

One date rule

Know your columns

Choose the grain

Bad date filters

Daily signups

Handle India time

Weekly orders

Do this now

6. Write cohort and funnel queries

One idea

Cohort meaning

Cohort query shape

Monthly retention

Funnel meaning

Ordered funnel

Common mistake

Do this now

Read the pattern

Rank features

Running revenue

Common mistake

Do this now

7. Check numbers before presenting

Trust is earned

What to check

Basic check flow

Checks query file

Do not hide mismatches

Use AI carefully

Write checked outputs

Do this now