Advanced SQL Interview Questions for Data Analyst

Advanced-level SQL interview questions for Data Analyst positions.

1. Write a query to find the running total and running average of daily sales.

Answer: Use SUM() and AVG() as window functions with ORDER BY but without PARTITION BY. This creates cumulative calculations across all ordered rows.

SELECT 
  sale_date,
  daily_sales,
  SUM(daily_sales) OVER (ORDER BY sale_date) AS running_total,
  ROUND(AVG(daily_sales) OVER (ORDER BY sale_date), 2) AS running_avg,
  SUM(daily_sales) OVER (
    ORDER BY sale_date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS last_7_days_total
FROM daily_sales;

2. How would you pivot data from rows to columns in SQL?

Answer: Use CASE WHEN inside aggregate functions or the PIVOT operator (in databases that support it). This transforms vertical data into horizontal format for reporting.

-- Manual pivot with CASE WHEN
SELECT 
  product_id,
  SUM(CASE WHEN month = 'Jan' THEN sales ELSE 0 END) AS jan_sales,
  SUM(CASE WHEN month = 'Feb' THEN sales ELSE 0 END) AS feb_sales,
  SUM(CASE WHEN month = 'Mar' THEN sales ELSE 0 END) AS mar_sales
FROM monthly_sales
GROUP BY product_id;

3. Write a query to calculate customer retention rate by cohort.

Answer: Group customers by signup month (cohort), then calculate how many returned in subsequent months. Use self-join or window functions with DISTINCT counting.

WITH first_purchase AS (
  SELECT customer_id, MIN(DATE_TRUNC('month', order_date)) AS cohort_month
  FROM orders
  GROUP BY customer_id
),
retention AS (
  SELECT 
    f.cohort_month,
    DATE_TRUNC('month', o.order_date) AS active_month,
    COUNT(DISTINCT o.customer_id) AS retained_customers
  FROM first_purchase f
  JOIN orders o ON f.customer_id = o.customer_id
  GROUP BY f.cohort_month, DATE_TRUNC('month', o.order_date)
)
SELECT 
  cohort_month,
  active_month,
  retained_customers,
  ROUND(retained_customers * 100.0 / FIRST_VALUE(retained_customers) 
    OVER (PARTITION BY cohort_month ORDER BY active_month), 2) AS retention_pct
FROM retention
ORDER BY cohort_month, active_month;