SQL for Analytics - lesson 4 | HackIndia Skills

SQL for Analytics - lesson 4 | HackIndia Skills | HackIndia

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;

Build readable queries with CTEs

One idea

Why CTEs matter

CTE query shape

Worked example

Name for reviewers

Common mistake

Use AI carefully

Do this now

Quick check