Gigsouk
Post a job

SQL Case Study Practice

Work through realistic business questions on a small e-commerce schema. Try each one yourself, then reveal a hint or the full solution.

The schema — an online store

  • customers (customer_id INT PK, name TEXT, country TEXT, signup_date DATE, acquisition_channel TEXT)
  • orders (order_id INT PK, customer_id INT FK, order_date DATE, status TEXT ('paid' | 'refunded' | 'cancelled'))
  • order_items (order_id INT FK, product_id INT FK, quantity INT, unit_price NUMERIC)
  • products (product_id INT PK, name TEXT, category TEXT, cost NUMERIC)

Revenue = quantity × unit_price on paid orders. Solutions use PostgreSQL syntax; DATE_TRUNC and INTERVAL differ slightly in MySQL, BigQuery and Snowflake.

  1. #1

    Monthly revenue

    Easy

    Show total paid revenue per month for 2026, newest month first.

  2. #2

    Top products by gross margin

    Easy

    List the 10 products with the highest total gross margin (revenue − cost) from paid orders.

  3. #3

    Customers who never ordered

    Easy

    Find customers who signed up in the last 90 days but haven't placed a paid order.

  4. #4

    Repeat purchase rate by channel

    Medium

    For each acquisition channel, what share of customers with at least one paid order went on to place a second?

  5. #5

    Each customer's first order

    Medium

    Return each customer's first paid order with its date and order value.

  6. #6

    Month-over-month growth

    Medium

    Show monthly paid revenue alongside the previous month's revenue and the percentage change.

  7. #7

    Cohort retention

    Hard

    Group customers by the month of their first paid order. For each cohort, what share ordered again in months 1, 2 and 3 after that?

  8. #8

    Refund-heavy categories

    Hard

    Which product categories have a refund rate (refunded revenue ÷ paid + refunded revenue) more than twice the overall rate?

Frequently asked questions

Which SQL dialect are the solutions in?

PostgreSQL. Date functions like DATE_TRUNC and INTERVAL differ slightly in MySQL, BigQuery and Snowflake, but the logic carries over.

How should I practise with these?

Write your own query first, then check the hint, then the solution. Explaining why the solution works is as important in interviews as writing it.

Which SQL topics do the case studies cover?

Aggregation, joins, anti-joins, CTEs, window functions (ROW_NUMBER, LAG), conditional aggregation and cohort retention.

More free tools

All tools →

Put your skills to work

Thousands of open roles from companies hiring now — apply directly with the employer.

Data & AI jobsBrowse all jobs