SQL for Analytics - lesson 4 | HackIndia Skills

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

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;

Tip

Name for reviewers

Use names that match business meaning. paid_orders is better than cte1. city_revenue is better than temp2.

Your hackathon reviewer may not run every query first. Good names help them understand your answer from the file itself.

Common mistake

Do not use a CTE just to hide messy logic. If one CTE has 40 lines, split it into smaller steps.

Also avoid selecting every column with SELECT * in your final project queries. Choose the columns needed for the answer, so your output is easy to check.

Use AI carefully

You can ask ChatGPT, Gemini, or Claude to rewrite a long query using CTEs. Give it your table names, columns, and the product question.

Then check three things yourself: the joins are still correct, filters have not changed, and the final numbers match your old query on sample data.