Practice — query a retail sales database — SQL & Relational Databases

What You'll Build

In this exercise, you will work as a data analyst for CricketGear India — India's largest cricket equipment retailer — whose transactional database contains sales orders, customers, products, and delivery regions. You will write a series of SQL queries of increasing complexity to answer real business questions: which products are top sellers, which regions have the highest average order value, which customers have not ordered in the last 90 days, and which product categories drive the most revenue. By the end of this exercise, you will have applied SELECT, WHERE, ORDER BY, LIMIT, GROUP BY, HAVING, JOINs, and CTEs in a realistic retail analytics context.

Each query you write mirrors a real data engineering task: extracting facts from a normalised OLTP schema, joining across multiple tables, applying business logic through WHERE and HAVING, and summarising results for a reporting layer. The schema you will query represents the kind of source database a data engineer extracts from when building an ELT pipeline — understanding it deeply, and writing correct extraction queries, is the foundational skill for every subsequent module in this course.

The exercise is structured in four progressive steps. Step 1 establishes the schema and sample data. Step 2 covers single-table queries using aggregation. Step 3 introduces multi-table JOINs for cross-entity analysis. Step 4 uses CTEs for multi-step analytical queries. Each step builds on the previous and produces a named query you can add to a portfolio of SQL analytical work.

Analogy🏏Cricket

🏏 Think of it like cricket: This exercise runs like a proper tournament bureau's production week, and the step order is the point. Step 1 is the pitch inspection before play: you verify the ground truth — no orphaned scorecard lines, no impossible totals — because an analysis built on a corrupt book is a match played on a dangerous pitch: everything after it is invalidated. Step 2 is the specialist coaches' reports: batting summaries with rankings and form lines, each an independent, checkable piece of work using window functions over the validated data. Step 3 is the selectors' composite: batting and bowling folded into one all-rounder view via conditional aggregation — the wide wall chart built from the long book. Step 4 is the match referee's reconciliation: the chart's totals must re-add to the book's totals exactly, or something was dropped or double-counted on the way. Validate, analyse, combine, reconcile — every production pipeline plays in that order.

Prerequisites

Setup & Project Structure

Create a dedicated database for this exercise and run the setup script to create the schema and load sample data. All queries in subsequent steps should be run in the cricketgear database. The schema uses four tables — customers, products, orders, and order_items — reflecting a standard retail star schema layout. The order_items table is the fact table (one row per line item), and the others are dimension-like tables. Keep your queries in a .sql file for version control and later reference.

Analogy🏏Cricket

🏏 Think of it like cricket: this setup is like inheriting a franchise's raw scoring archive before an analytics project — six boxes of records where team names are spelled three ways and player details are copied redundantly onto every card. Just as real archives are messy because they were built for recording matches, not for analysis, this schema is deliberately denormalised to mirror the source databases data engineers actually meet, where cleaning and normalising come before any insight. Just as an analyst hired by an IPL franchise would keep every derivation in one working file — so the head coach can audit exactly how each conclusion was reached — you keep all solutions in a single ipl_analytics.sql, which doubles as your portfolio deliverable, the way a match-analysis dossier proves a cricket analyst's craft to the next employer. The payoff: practising on realistically imperfect data, with a reviewable artefact at the end, is what turns textbook SQL into the working habits of a professional data engineer.

Step 1 — Foundation: Verify Schema and Explore Data

Before writing analytical queries, always explore the schema to understand table structures, row counts, and data distributions. This step mirrors what a data engineer does when connecting to an unfamiliar source database for the first time — understanding what is in the tables and verifying that constraints are working correctly before building a pipeline on top of them. Write and run each of the following exploratory queries and note the results.

Analogy🏏Cricket

🏏 Think of it like cricket: exploring the schema first is what a professional does before a match on an unfamiliar ground — walking the pitch, checking the boundary dimensions, reading the ground's scoring history before choosing a game plan. Just as a captain wouldn't set a field without knowing whether the square boundary is 60 or 80 metres, a data engineer never builds a pipeline without knowing table structures, row counts, and value distributions in the source database. Just as inspecting the pitch reveals whether it will seam or spin — shaping every later decision — exploratory queries reveal which columns hold NULLs, how orders distribute across customers, and whether constraints are actually enforcing what they claim. And just as pre-match routines catch problems (a damp patch, a short boundary rope) while there is still time to adapt, schema exploration catches surprises before they are baked into transformation logic. The payoff: every analytical query you write afterwards stands on verified ground rather than assumptions.

Step 2 — Core Logic: Aggregation and Filtering

Write the four analytical queries below. Each query answers a specific business question about CricketGear India's sales. Focus on correct GROUP BY column selection, the distinction between WHERE (pre-aggregation) and HAVING (post-aggregation), and NULL handling in aggregate expressions. Compare your results against the expected values provided in the code comments to verify your queries are correct before proceeding to Step 3.

Analogy🏏Cricket

🏏 Think of it like cricket: these four queries are your net session for aggregation — each one a specific drill with a known correct outcome, like facing throwdowns where the coach knows exactly where a good shot should go. Just as a batter drills the difference between leaving a ball outside off (filtering deliveries before they count — WHERE) and reviewing which completed innings met a milestone (judging totals after they're tallied — HAVING), these exercises train the pre-aggregation versus post-aggregation distinction until it is instinct. Just as a tally is wrong if two same-named players are merged, correct GROUP BY column selection keeps each business entity distinct. And comparing your results against the expected values in the comments is like checking your net-session numbers against the coach's sheet — immediate feedback on whether the technique held up. The payoff: when a real business question arrives at match pace, the WHERE/HAVING decision and the grouping key choice are already automatic.

Step 3 — Integration & Enhancement: Multi-Step CTE Analysis

Combine everything from Steps 1 and 2 into a single multi-step CTE query that produces a customer value report: for each customer, compute total spend, number of orders, days since last order, and a value segment label (High Value, Regular, At Risk, or Dormant based on recency and spend). This mirrors a real-world RFM (Recency, Frequency, Monetary) analysis used by e-commerce platforms to segment customers for targeted marketing.

Analogy🏏Cricket

🏏 Think of it like cricket: The customer value report in Step 3 is built the way a selector builds an end-of-season player review. Sheet one (first CTE): each player's raw season tallies — runs, matches, dismissals — one line per player, straight aggregation. Sheet two (second CTE): derived judgements computed from sheet one, like average and strike rate, plus a form band ('in form' / 'steady' / 'out of form') assigned with CASE-style thresholds. The final SELECT is the ranked shortlist handed to the head coach: sheet two's rows ordered by the metric that matters, top names first. The discipline to copy is that no judgement is computed from raw deliveries directly — each sheet reads only the sheet before it, so when a player's band looks wrong you check sheet one's tally in isolation instead of re-auditing the whole season's scorebook. That is exactly how a multi-step CTE keeps a business report debuggable.

Step 4 — Testing & Verification

Run the verification queries below to confirm your Step 2 and Step 3 results are internally consistent. Cross-check total revenue from the category breakdown against the total revenue from the product breakdown — they should be equal. Verify that every customer appears in the final RFM report (including those with no orders). Data engineers always run reconciliation checks after transformation queries before promoting results to a reporting layer.

Analogy🏏Cricket

🏏 Think of it like cricket: final reconciliation is how a tournament's official statistician signs off the season book: the runs credited to all-rounders in the combined report must equal the runs in the batting ledger, and their wickets must equal the bowling ledger's total — three independently kept tallies that must agree before anything is published. Just as a mismatch tells the scorer that a card was double-counted or a player's bowling figures never got attached to their batting record, a discrepancy here indicates a JOIN that multiplied rows or a missing COALESCE letting NULLs eat values silently. Just as the statistician must also explain every irregularity noted during the season in the final report, you document the Step 1 anomaly and explain precisely how WHERE b.batting_pos IS NOT NULL handles it — an auditable justification, not a silent exclusion. The payoff: results cross-checked from independent directions and anomalies explained in writing — the standard that lets a data engineer promote numbers to production with confidence.