Project: e-commerce analytics, and your next steps

The questions a shop asks

An e-commerce team looks at a few numbers every week. Each is a short query once you have the building blocks from this course. In all of them, Cancelled orders are left out of revenue; agree such rules with the business first, because a different rule gives a different number.

Order totals first

Most shop metrics start from one number per order: SUM(qty * price) of its lines. Put it in a CTE called totals and reuse it.

WITH totals AS (
  SELECT o.id, o.customer_id, SUM(oi.qty * oi.price) AS total
  FROM orders o JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status <> 'Cancelled'
  GROUP BY o.id)
SELECT COUNT(*) AS orders, SUM(total) AS revenue, ROUND(AVG(total), 2) AS aov
FROM totals;
-- 7 | 13844 | 1977.71

Monthly revenue

SELECT strftime('%Y-%m', o.order_date) AS month, SUM(oi.qty * oi.price) AS revenue
FROM orders o JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'Cancelled'
GROUP BY month ORDER BY month;
-- 2025-01 | 3779
-- 2025-02 | 2248
-- 2025-03 | 7167
-- 2025-04 | 650

Repeat customers and customers with no orders

A repeat customer has two or more (delivered) orders: GROUP BY customer, HAVING COUNT(*) >= 2. Asha Verma and Ravi Kumar qualify. Customers with no orders need an anti-join: Arjun Reddy has never ordered.

SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;          -- 6 | Arjun Reddy

Top products

SELECT p.name, SUM(oi.qty * oi.price) AS revenue
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
WHERE o.status <> 'Cancelled'
GROUP BY p.id, p.name
ORDER BY revenue DESC;
-- Bluetooth Earbuds | 3998
-- Cotton Kurta      | 2697
-- Running Shoes     | 2499
-- ...

A simple cohort

A cohort is a group of customers who started at the same time, such as everyone who joined in January 2024. Comparing cohorts shows whether newer customers buy as much as older ones. The simplest version counts, per join month, how many customers joined and how many of them have ordered:

SELECT strftime('%Y-%m', c.joined) AS cohort,
       COUNT(DISTINCT c.id) AS customers,
       COUNT(DISTINCT o.customer_id) AS buyers
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status <> 'Cancelled'
GROUP BY cohort ORDER BY cohort;
-- 2024-01 | 1 | 1
-- 2024-02 | 1 | 1
-- 2024-03 | 1 | 0
-- ...

Your next steps

1. Practise daily for a few weeks: LeetCode's database problems, HackerRank's SQL track, SQLZoo, SQLBolt and StrataScratch all have free questions. Mix easy and medium.
2. Build a portfolio: pick a public dataset (for example from data.gov.in or Kaggle), load it into SQLite or PostgreSQL, write 10–15 queries that answer real questions, and publish the SQL with short explanations on GitHub.
3. Learn one server engine well (PostgreSQL is a good default), plus one BI tool such as Power BI or Tableau if you aim for analyst roles, or one backend framework if you aim for developer roles.

Jobs that use SQL every day

These ranges are rough, from public job listings and salary surveys; pay depends a lot on city, company, skills and the market in the year you apply, so check current listings.

Certifications

Certifications are optional: a good portfolio often matters more. If you want one, Microsoft, Oracle, AWS and Google all offer database and data certifications, from fundamentals to administrator and data engineer levels. Names and exam codes change often, so check the current exam page on the vendor's website before you prepare.

Common mistakes

• Counting cancelled orders as revenue. Agree the rule and write it in every query (or in a view).
• Computing AOV as the average of order lines instead of orders. Total each order first.
• Putting a filter on the right-hand table of a LEFT JOIN in WHERE, which silently turns it into an inner join.
• Collecting certificates instead of building and showing real queries.