SQL Case Study: Answer a Business Question End to End

A dashboard can say “sales are down,” but it cannot tell you whether the problem is traffic, conversion, payment failure, stockouts, cancellations or repeat behavior. A weak analyst writes a query; a strong analyst turns that messy business question into a defensible decision.

The Big Picture: SQL Is the Middle, Not the Starting Point

The interviewer is rarely testing whether you remember every SQL keyword. They are testing whether you can convert ambiguity into an analytical path, use SQL to extract evidence, and make a recommendation that a business team can act on.

The End-to-End SQL Case Framework

Use this six-step structure whenever you get a business question such as “Why did revenue drop?”, “Which users should we target?”, or “Did the campaign improve repeat purchases?”

The Difference Between a Query-Only Answer and a Business-Ready Answer

Many candidates can write a GROUP BY. Fewer candidates can defend why that grouping answers the business question. This is the contrast interviewers listen for.

Core SQL Logic: The Order Your Brain Should Follow

SQL is written in one order but logically processed in another. For case interviews, think in the logical order: first build the dataset, then filter it, then aggregate it, then present the answer.

Business Metrics You Should Be Ready to Calculate in SQL

There is no universal “good” conversion rate or repeat rate across categories. In interviews, call this out and benchmark against the right baseline: same weekday, same cohort, same city, same category, or pre-period.

Worked Example: Answer a Before-After Repeat Purchase Question

Business question: “Did our February campaign improve repeat purchasing in Bengaluru compared with January?” The correct answer requires a clear metric definition, not just total orders.

Metric definition: Repeat purchase rate = customers with at least 2 delivered orders in the month / customers with at least 1 delivered order in the month.

One clean SQL pattern is to first create customer-month rows, then aggregate them. The exact date function may vary by SQL dialect, but the logic stays the same.

WITH delivered_orders AS (
  SELECT
    order_id,
    customer_id,
    DATE_TRUNC('month', order_date) AS order_month,
    amount
  FROM orders
  WHERE city = 'Bengaluru'
    AND status = 'delivered'
),

customer_month AS (
  SELECT
    order_month,
    customer_id,
    COUNT(*) AS delivered_orders,
    SUM(amount) AS delivered_gmv
  FROM delivered_orders
  GROUP BY order_month, customer_id
)

SELECT
  order_month,
  COUNT(*) AS buyers,
  SUM(CASE WHEN delivered_orders >= 2 THEN 1 ELSE 0 END) AS repeat_buyers,
  ROUND(
    100.0 * SUM(CASE WHEN delivered_orders >= 2 THEN 1 ELSE 0 END)
    / NULLIF(COUNT(*), 0),
    1
  ) AS repeat_purchase_rate_pct,
  SUM(delivered_gmv) AS delivered_gmv
FROM customer_month
GROUP BY order_month
ORDER BY order_month;

Business answer: The campaign did not improve repeat purchase rate in this sample; it stayed at 33.3%. Delivered GMV rose slightly, but the driver was higher value per order, not better repeat behavior. The next cut should segment by acquisition source, discount usage and category to see whether the campaign brought new buyers without improving loyalty.

Definitions You Can Say Clearly

Case Study: Nykaa - Diagnosing a Repeat Commerce Problem

Nykaa operates in a category where discovery matters: a shopper may browse skincare, compare shades, wait for discounts, add products to cart, and return only if the product experience and delivery promise are strong. So a question like “Why did repeat revenue fall?” cannot be answered by looking at revenue alone.

Situation: Imagine the growth team sees repeat revenue soften in one category while app traffic remains healthy. A shallow answer would blame marketing immediately. A stronger SQL analyst separates the problem into buyer cohorts, product categories, discount exposure, stock availability, delivery outcomes and cancellations.

The move: The analyst builds a layered SQL analysis: first identifies repeat customers, then joins orders to product category and fulfilment status, then compares cohorts across months. The primary driver must be isolated from supporting drivers. For example, if repeat revenue falls mainly among skincare buyers, the analyst should test whether the cause is lower conversion, lower AOV, out-of-stock items, delayed delivery, lower discounting, or a category-mix shift.

Outcome or lesson: The best answer is not “Nykaa should give more discounts.” It is: “The repeat revenue dip is chiefly from fewer repeat buyers in a specific category; supporting signals are stock availability and category mix. I would prioritize replenishment and cohort retargeting before increasing blanket discounts.” That is a business answer, not just a SQL output.

How AI Changes Answering a Business Question End to End in SQL

AI will not remove SQL from analytics interviews; it raises the bar. If a tool can draft syntax, the candidate must be better at defining the metric, spotting wrong assumptions and explaining the business implication.

• Text-to-SQL is becoming normal: Tools can convert “show repeat purchase rate by month” into a draft query, but they often guess joins, grain and filters. Your job is to verify the business definition.
• AI-assisted data discovery is faster: LLMs can summarize table schemas, suggest likely join keys and identify missing fields. This helps when a case gives multiple tables such as users, orders, payments and events.
• AI improves QA and anomaly checks: You can ask for edge cases: duplicate order IDs, null customer IDs, cancelled orders, timezone cutoffs, late-arriving events and one-to-many joins.

Interview Relevance

Common Mistake

What to Revise Next

This is the capstone moment: do one full mock analytics case from scratch. Pick any business question, write the metric tree, sketch the tables, draft the SQL, list QA checks, and end with a recommendation in three sentences.