Ten SQL Patterns to Answer Most Analytics Interview Questions

A city manager opens the morning dashboard and sees food-delivery orders down 12% in one zone - but revenue is flat. The analyst who can separate a true demand drop from a coupon, payment, inventory or repeat-customer effect will not write exotic SQL; they will combine a few reliable patterns cleanly.

The big picture: SQL interviews are business-diagnosis interviews. The interviewer changes table names, but the logic stays the same - start from the question, choose the grain, build the query in layers, then explain the insight.

The Core Idea: Learn Patterns, Not Isolated Questions

A typical SQL question sounds specific: “Find the top three products by revenue in each city last month.” Under the surface, it is a reusable recipe: filter dates, join products to orders, aggregate revenue, rank within city and keep rank less than or equal to three.

Before writing any SQL, say the grain out loud: “My final output will have one row per city-product.” This single sentence prevents most join mistakes, double counting and wrong aggregations.

The Ten SQL Patterns That Cover Most Questions

Pattern 1 to 3: From Rows to Metrics

Filtering answers “which records qualify?” Aggregation answers “what is the metric at this grain?” Conditional aggregation is the interview favourite because it creates multiple business metrics from the same table scan.

Example: for a marketplace, one query can calculate total orders, prepaid orders and cancelled orders by city using three conditional sums. This is cleaner than writing three separate queries and joining them later.

SELECT
  city,
  COUNT(*) AS total_orders,
  SUM(CASE WHEN payment_mode = 'prepaid' THEN 1 ELSE 0 END) AS prepaid_orders,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_orders
FROM orders
WHERE order_date >= DATE '2026-01-01'
GROUP BY city;

Pattern 4 and 5: Joins Without Double Counting

A join is safe only when you know the relationship: one-to-one, one-to-many or many-to-many. If one order has three order items, joining orders to order_items will create three rows for that order. That is correct for item revenue, but wrong for counting orders unless you use COUNT(DISTINCT order_id).

Pattern 6 to 8: CTEs, Windows and Top-N

A CTE - common table expression - is a named temporary result inside a query. Use it to make your answer readable: base data first, metric calculation second, ranking or filtering third.

A window function calculates across related rows while keeping each row visible. Unlike GROUP BY, it does not collapse rows. That is why it is perfect for rank, previous purchase date, running total and contribution percentage.

WITH product_revenue AS (
  SELECT
    city,
    product_id,
    SUM(amount) AS revenue
  FROM order_items
  GROUP BY city, product_id
),
ranked AS (
  SELECT
    city,
    product_id,
    revenue,
    ROW_NUMBER() OVER (PARTITION BY city ORDER BY revenue DESC) AS rn
  FROM product_revenue
)
SELECT city, product_id, revenue
FROM ranked
WHERE rn <= 3;

Pattern 9 and 10: Dates, Cohorts and Sets

Date questions test whether you understand time as a business dimension. “Revenue by month” is simple date bucketing. “Customers retained after first order” is a cohort question. “Users active this month but not last month” is a set comparison.

Use UNION ALL when you want to stack records and preserve duplicates. Use UNION when you want duplicates removed. In interviews, say this explicitly because it proves you know both correctness and performance.

Worked Example: Find Repeat Purchase Rate by Month

Question: “For each month, what percentage of ordering customers are repeat customers?”

Logic: first rank each customer’s orders by date. Then mark orders with rank greater than one as repeat. Finally aggregate by month.

WITH ranked_orders AS (
  SELECT
    order_id,
    customer_id,
    order_month,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_id
    ) AS order_no
  FROM orders
)
SELECT
  order_month,
  COUNT(DISTINCT customer_id) AS ordering_customers,
  COUNT(DISTINCT CASE WHEN order_no > 1 THEN customer_id END) AS repeat_customers,
  100.0 * COUNT(DISTINCT CASE WHEN order_no > 1 THEN customer_id END)
        / COUNT(DISTINCT customer_id) AS repeat_rate_pct
FROM ranked_orders
GROUP BY order_month;

The business answer is not just “66.7%.” It is: “In February, two of three customers had ordered before, so growth came more from returning customers than purely new acquisition.”

Quality Checks: The Five Numbers That Catch Wrong SQL

Good analysts test the query before defending the insight. These checks are simple, concrete and interview-friendly.

Definitions You Should Be Able to Say in One Breath

BookMyShow: SQL Patterns in a Real Ticketing Marketplace

Situation: In a ticketing marketplace, demand can spike around blockbuster films, cricket matches, concerts or comedy shows. A manager may ask: “Why did successful bookings fall in a city yesterday even though traffic looked healthy?”

The move: A strong analyst would not start with one metric. They would decompose the funnel using SQL patterns: filter yesterday’s city traffic, join sessions to seat inventory and payment attempts, aggregate at event-hour grain, use conditional aggregation for success and failure reasons, and compare with previous days using windows. The primary driver might be seat availability or payment failure; supporting drivers could include show timing, offer eligibility, device mix or cancellation rules.

Outcome or lesson: The SQL answer becomes operational: add payment retries, change offer visibility, improve inventory refresh or alert partners where seat data is stale. The lesson is bigger than BookMyShow - marketplace analytics needs joins and time windows because customer demand, supply and transaction success sit in different tables.

So what: The win does not come from “knowing SQL syntax.” It comes from using SQL to isolate the primary business driver, then checking supporting drivers before recommending action.

How AI Changes SQL Patterns

AI is changing SQL preparation in three practical ways, but it does not remove the need for pattern thinking.

1. Natural-language SQL drafting: Tools can convert “show monthly repeat purchase rate” into a first SQL draft. Your job is to verify grain, joins, date logic and null handling.
2. Query explanation and debugging: LLMs can explain why a query duplicates rows or why a window function gives unexpected ranks. This is especially useful for CTE-heavy interview practice.
3. Semantic-layer awareness: Modern analytics teams increasingly define trusted metrics such as GMV, active customer and cancellation rate in semantic layers. AI can help discover definitions, but interviews still reward candidates who ask, “How is this metric defined?”

Interview Relevance

Common Mistake

What to Revise Next

Now move from pattern practice to full business diagnosis: revise Case Study: Answering a Business Question End to End in SQL. That is where you combine schema reading, metric definition, query writing, validation and insight into one interview-ready answer.