Interview Prep

SQL Interview Questions for Data Analyst (2026)

45 Questions

With Answers

15 Basic15 Intermediate15 Advanced

Quick Navigation

Basic Questions (15)Intermediate (15)Advanced (15)

Basic SQL Questions

Q1. What is the difference between WHERE and HAVING clause?

basic

Answer

WHERE filters rows before grouping, while HAVING filters groups after aggregation. WHERE cannot use aggregate functions, but HAVING can. Example: WHERE filters individual sales records, HAVING filters groups of sales by region.

Example Code

SQL

1-- WHERE: Filter rows before aggregation
2SELECT department, AVG(salary)
3FROM employees
4WHERE hire_date > '2020-01-01'
5GROUP BY department;
6
7-- HAVING: Filter after aggregation
8SELECT department, AVG(salary)
9FROM employees
10GROUP BY department
11HAVING AVG(salary) > 50000;

Q1. What is the difference between WHERE and HAVING clause?

basic

Answer

Example Code

SQL

1-- WHERE: Filter rows before aggregation
2SELECT department, AVG(salary)
3FROM employees
4WHERE hire_date > '2020-01-01'
5GROUP BY department;
6
7-- HAVING: Filter after aggregation
8SELECT department, AVG(salary)
9FROM employees
10GROUP BY department
11HAVING AVG(salary) > 50000;

Q1. What is the difference between WHERE and HAVING clause?

basic

Answer

Example Code

SQL

1-- WHERE: Filter rows before aggregation
2SELECT department, AVG(salary)
3FROM employees
4WHERE hire_date > '2020-01-01'
5GROUP BY department;
6
7-- HAVING: Filter after aggregation
8SELECT department, AVG(salary)
9FROM employees
10GROUP BY department
11HAVING AVG(salary) > 50000;

Q1. What is the difference between WHERE and HAVING clause?

basic

Answer

Example Code

SQL

1-- WHERE: Filter rows before aggregation
2SELECT department, AVG(salary)
3FROM employees
4WHERE hire_date > '2020-01-01'
5GROUP BY department;
6
7-- HAVING: Filter after aggregation
8SELECT department, AVG(salary)
9FROM employees
10GROUP BY department
11HAVING AVG(salary) > 50000;

Q1. What is the difference between WHERE and HAVING clause?

basic

Answer

Example Code

SQL

1-- WHERE: Filter rows before aggregation
2SELECT department, AVG(salary)
3FROM employees
4WHERE hire_date > '2020-01-01'
5GROUP BY department;
6
7-- HAVING: Filter after aggregation
8SELECT department, AVG(salary)
9FROM employees
10GROUP BY department
11HAVING AVG(salary) > 50000;

Q2. Explain the difference between INNER JOIN and LEFT JOIN.

basic

Answer

INNER JOIN returns only matching rows from both tables. LEFT JOIN returns all rows from the left table and matching rows from the right table (NULL for non-matches). Use LEFT JOIN when you need to keep all records from one table regardless of matches.

Example Code

SQL

1-- INNER JOIN: Only matched records
2SELECT c.name, o.order_id
3FROM customers c
4INNER JOIN orders o ON c.id = o.customer_id;
5
6-- LEFT JOIN: All customers, even without orders
7SELECT c.name, o.order_id
8FROM customers c
9LEFT JOIN orders o ON c.id = o.customer_id;

Q2. Explain the difference between INNER JOIN and LEFT JOIN.

basic

Answer

Example Code

SQL

1-- INNER JOIN: Only matched records
2SELECT c.name, o.order_id
3FROM customers c
4INNER JOIN orders o ON c.id = o.customer_id;
5
6-- LEFT JOIN: All customers, even without orders
7SELECT c.name, o.order_id
8FROM customers c
9LEFT JOIN orders o ON c.id = o.customer_id;

Q2. Explain the difference between INNER JOIN and LEFT JOIN.

basic

Answer

Example Code

SQL

1-- INNER JOIN: Only matched records
2SELECT c.name, o.order_id
3FROM customers c
4INNER JOIN orders o ON c.id = o.customer_id;
5
6-- LEFT JOIN: All customers, even without orders
7SELECT c.name, o.order_id
8FROM customers c
9LEFT JOIN orders o ON c.id = o.customer_id;

Q2. Explain the difference between INNER JOIN and LEFT JOIN.

basic

Answer

Example Code

SQL

1-- INNER JOIN: Only matched records
2SELECT c.name, o.order_id
3FROM customers c
4INNER JOIN orders o ON c.id = o.customer_id;
5
6-- LEFT JOIN: All customers, even without orders
7SELECT c.name, o.order_id
8FROM customers c
9LEFT JOIN orders o ON c.id = o.customer_id;

Q2. Explain the difference between INNER JOIN and LEFT JOIN.

basic

Answer

Example Code

SQL

1-- INNER JOIN: Only matched records
2SELECT c.name, o.order_id
3FROM customers c
4INNER JOIN orders o ON c.id = o.customer_id;
5
6-- LEFT JOIN: All customers, even without orders
7SELECT c.name, o.order_id
8FROM customers c
9LEFT JOIN orders o ON c.id = o.customer_id;

Q3. What are aggregate functions? Name five common ones.

basic

Answer

Aggregate functions perform calculations on sets of rows and return a single value. Five common ones: COUNT() - counts rows, SUM() - adds values, AVG() - calculates average, MAX() - finds maximum, MIN() - finds minimum.

Example Code

SQL

1SELECT 
2  COUNT(*) AS total_orders,
3  SUM(amount) AS total_revenue,
4  AVG(amount) AS avg_order_value,
5  MAX(amount) AS largest_order,
6  MIN(amount) AS smallest_order
7FROM orders;

Q3. What are aggregate functions? Name five common ones.

basic

Answer

Example Code

SQL

1SELECT 
2  COUNT(*) AS total_orders,
3  SUM(amount) AS total_revenue,
4  AVG(amount) AS avg_order_value,
5  MAX(amount) AS largest_order,
6  MIN(amount) AS smallest_order
7FROM orders;

Q3. What are aggregate functions? Name five common ones.

basic

Answer

Example Code

SQL

1SELECT 
2  COUNT(*) AS total_orders,
3  SUM(amount) AS total_revenue,
4  AVG(amount) AS avg_order_value,
5  MAX(amount) AS largest_order,
6  MIN(amount) AS smallest_order
7FROM orders;

Q3. What are aggregate functions? Name five common ones.

basic

Answer

Example Code

SQL

1SELECT 
2  COUNT(*) AS total_orders,
3  SUM(amount) AS total_revenue,
4  AVG(amount) AS avg_order_value,
5  MAX(amount) AS largest_order,
6  MIN(amount) AS smallest_order
7FROM orders;

Q3. What are aggregate functions? Name five common ones.

basic

Answer

Example Code

SQL

1SELECT 
2  COUNT(*) AS total_orders,
3  SUM(amount) AS total_revenue,
4  AVG(amount) AS avg_order_value,
5  MAX(amount) AS largest_order,
6  MIN(amount) AS smallest_order
7FROM orders;

Intermediate SQL Questions

Q4. Write a query to find duplicate records in a table.

intermediate

Answer

Use GROUP BY with HAVING COUNT(*) > 1 to find duplicates. You can also use ROW_NUMBER() window function to identify and remove duplicates while keeping one copy.

Example Code

SQL

1-- Find duplicate emails
2SELECT email, COUNT(*) as count
3FROM users
4GROUP BY email
5HAVING COUNT(*) > 1;
6
7-- Find and number duplicates
8WITH numbered AS (
9  SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as rn
10  FROM users
11)
12SELECT * FROM numbered WHERE rn > 1;

Q4. Write a query to find duplicate records in a table.

intermediate

Answer

Use GROUP BY with HAVING COUNT(*) > 1 to find duplicates. You can also use ROW_NUMBER() window function to identify and remove duplicates while keeping one copy.

Example Code

SQL

1-- Find duplicate emails
2SELECT email, COUNT(*) as count
3FROM users
4GROUP BY email
5HAVING COUNT(*) > 1;
6
7-- Find and number duplicates
8WITH numbered AS (
9  SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as rn
10  FROM users
11)
12SELECT * FROM numbered WHERE rn > 1;

Q4. Write a query to find duplicate records in a table.

intermediate

Answer

Use GROUP BY with HAVING COUNT(*) > 1 to find duplicates. You can also use ROW_NUMBER() window function to identify and remove duplicates while keeping one copy.

Example Code

SQL

1-- Find duplicate emails
2SELECT email, COUNT(*) as count
3FROM users
4GROUP BY email
5HAVING COUNT(*) > 1;
6
7-- Find and number duplicates
8WITH numbered AS (
9  SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as rn
10  FROM users
11)
12SELECT * FROM numbered WHERE rn > 1;

Q4. Write a query to find duplicate records in a table.

intermediate

Answer

Use GROUP BY with HAVING COUNT(*) > 1 to find duplicates. You can also use ROW_NUMBER() window function to identify and remove duplicates while keeping one copy.

Example Code

SQL

1-- Find duplicate emails
2SELECT email, COUNT(*) as count
3FROM users
4GROUP BY email
5HAVING COUNT(*) > 1;
6
7-- Find and number duplicates
8WITH numbered AS (
9  SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as rn
10  FROM users
11)
12SELECT * FROM numbered WHERE rn > 1;

Q4. Write a query to find duplicate records in a table.

intermediate

Answer

Use GROUP BY with HAVING COUNT(*) > 1 to find duplicates. You can also use ROW_NUMBER() window function to identify and remove duplicates while keeping one copy.

Example Code

SQL

1-- Find duplicate emails
2SELECT email, COUNT(*) as count
3FROM users
4GROUP BY email
5HAVING COUNT(*) > 1;
6
7-- Find and number duplicates
8WITH numbered AS (
9  SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as rn
10  FROM users
11)
12SELECT * FROM numbered WHERE rn > 1;

Q5. How would you calculate month-over-month growth rate?

intermediate

Answer

Use LAG() window function to access the previous month's value, then calculate percentage change: (current - previous) / previous * 100.

Example Code

SQL

1WITH monthly AS (
2  SELECT 
3    DATE_TRUNC('month', order_date) AS month,
4    SUM(revenue) AS revenue
5  FROM orders
6  GROUP BY DATE_TRUNC('month', order_date)
7)
8SELECT 
9  month,
10  revenue,
11  LAG(revenue) OVER (ORDER BY month) AS prev_month,
12  ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) / 
13    LAG(revenue) OVER (ORDER BY month) * 100, 2) AS growth_pct
14FROM monthly;

Q5. How would you calculate month-over-month growth rate?

intermediate

Answer

Use LAG() window function to access the previous month's value, then calculate percentage change: (current - previous) / previous * 100.

Example Code

SQL

1WITH monthly AS (
2  SELECT 
3    DATE_TRUNC('month', order_date) AS month,
4    SUM(revenue) AS revenue
5  FROM orders
6  GROUP BY DATE_TRUNC('month', order_date)
7)
8SELECT 
9  month,
10  revenue,
11  LAG(revenue) OVER (ORDER BY month) AS prev_month,
12  ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) / 
13    LAG(revenue) OVER (ORDER BY month) * 100, 2) AS growth_pct
14FROM monthly;

Q5. How would you calculate month-over-month growth rate?

intermediate

Answer

Use LAG() window function to access the previous month's value, then calculate percentage change: (current - previous) / previous * 100.

Example Code

SQL

1WITH monthly AS (
2  SELECT 
3    DATE_TRUNC('month', order_date) AS month,
4    SUM(revenue) AS revenue
5  FROM orders
6  GROUP BY DATE_TRUNC('month', order_date)
7)
8SELECT 
9  month,
10  revenue,
11  LAG(revenue) OVER (ORDER BY month) AS prev_month,
12  ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) / 
13    LAG(revenue) OVER (ORDER BY month) * 100, 2) AS growth_pct
14FROM monthly;

Q5. How would you calculate month-over-month growth rate?

intermediate

Answer

Use LAG() window function to access the previous month's value, then calculate percentage change: (current - previous) / previous * 100.

Example Code

SQL

1WITH monthly AS (
2  SELECT 
3    DATE_TRUNC('month', order_date) AS month,
4    SUM(revenue) AS revenue
5  FROM orders
6  GROUP BY DATE_TRUNC('month', order_date)
7)
8SELECT 
9  month,
10  revenue,
11  LAG(revenue) OVER (ORDER BY month) AS prev_month,
12  ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) / 
13    LAG(revenue) OVER (ORDER BY month) * 100, 2) AS growth_pct
14FROM monthly;

Q5. How would you calculate month-over-month growth rate?

intermediate

Answer

Use LAG() window function to access the previous month's value, then calculate percentage change: (current - previous) / previous * 100.

Example Code

SQL

1WITH monthly AS (
2  SELECT 
3    DATE_TRUNC('month', order_date) AS month,
4    SUM(revenue) AS revenue
5  FROM orders
6  GROUP BY DATE_TRUNC('month', order_date)
7)
8SELECT 
9  month,
10  revenue,
11  LAG(revenue) OVER (ORDER BY month) AS prev_month,
12  ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) / 
13    LAG(revenue) OVER (ORDER BY month) * 100, 2) AS growth_pct
14FROM monthly;

Q6. Explain the difference between RANK(), DENSE_RANK(), and ROW_NUMBER().

intermediate

Answer

ROW_NUMBER() assigns unique sequential numbers (1,2,3,4). RANK() gives same rank to ties but skips numbers (1,2,2,4). DENSE_RANK() gives same rank to ties without skipping (1,2,2,3). Choose based on how you want to handle ties.

Example Code

SQL

1SELECT 
2  name,
3  score,
4  ROW_NUMBER() OVER (ORDER BY score DESC) as row_num,
5  RANK() OVER (ORDER BY score DESC) as rank,
6  DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank
7FROM students;
8-- If two students have same score:
9-- row_num: 1,2,3  rank: 1,1,3  dense_rank: 1,1,2

Q6. Explain the difference between RANK(), DENSE_RANK(), and ROW_NUMBER().

intermediate

Answer

Example Code

SQL

1SELECT 
2  name,
3  score,
4  ROW_NUMBER() OVER (ORDER BY score DESC) as row_num,
5  RANK() OVER (ORDER BY score DESC) as rank,
6  DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank
7FROM students;
8-- If two students have same score:
9-- row_num: 1,2,3  rank: 1,1,3  dense_rank: 1,1,2

Q6. Explain the difference between RANK(), DENSE_RANK(), and ROW_NUMBER().

intermediate

Answer

Example Code

SQL

1SELECT 
2  name,
3  score,
4  ROW_NUMBER() OVER (ORDER BY score DESC) as row_num,
5  RANK() OVER (ORDER BY score DESC) as rank,
6  DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank
7FROM students;
8-- If two students have same score:
9-- row_num: 1,2,3  rank: 1,1,3  dense_rank: 1,1,2

Q6. Explain the difference between RANK(), DENSE_RANK(), and ROW_NUMBER().

intermediate

Answer

Example Code

SQL

1SELECT 
2  name,
3  score,
4  ROW_NUMBER() OVER (ORDER BY score DESC) as row_num,
5  RANK() OVER (ORDER BY score DESC) as rank,
6  DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank
7FROM students;
8-- If two students have same score:
9-- row_num: 1,2,3  rank: 1,1,3  dense_rank: 1,1,2

Q6. Explain the difference between RANK(), DENSE_RANK(), and ROW_NUMBER().

intermediate

Answer

Example Code

SQL

1SELECT 
2  name,
3  score,
4  ROW_NUMBER() OVER (ORDER BY score DESC) as row_num,
5  RANK() OVER (ORDER BY score DESC) as rank,
6  DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank
7FROM students;
8-- If two students have same score:
9-- row_num: 1,2,3  rank: 1,1,3  dense_rank: 1,1,2

Advanced SQL Questions

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

advanced

Answer

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

Example Code

SQL

1SELECT 
2  sale_date,
3  daily_sales,
4  SUM(daily_sales) OVER (ORDER BY sale_date) AS running_total,
5  ROUND(AVG(daily_sales) OVER (ORDER BY sale_date), 2) AS running_avg,
6  SUM(daily_sales) OVER (
7    ORDER BY sale_date 
8    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
9  ) AS last_7_days_total
10FROM daily_sales;

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

advanced

Answer

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

Example Code

SQL

1SELECT 
2  sale_date,
3  daily_sales,
4  SUM(daily_sales) OVER (ORDER BY sale_date) AS running_total,
5  ROUND(AVG(daily_sales) OVER (ORDER BY sale_date), 2) AS running_avg,
6  SUM(daily_sales) OVER (
7    ORDER BY sale_date 
8    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
9  ) AS last_7_days_total
10FROM daily_sales;

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

advanced

Answer

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

Example Code

SQL

1SELECT 
2  sale_date,
3  daily_sales,
4  SUM(daily_sales) OVER (ORDER BY sale_date) AS running_total,
5  ROUND(AVG(daily_sales) OVER (ORDER BY sale_date), 2) AS running_avg,
6  SUM(daily_sales) OVER (
7    ORDER BY sale_date 
8    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
9  ) AS last_7_days_total
10FROM daily_sales;

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

advanced

Answer

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

Example Code

SQL

1SELECT 
2  sale_date,
3  daily_sales,
4  SUM(daily_sales) OVER (ORDER BY sale_date) AS running_total,
5  ROUND(AVG(daily_sales) OVER (ORDER BY sale_date), 2) AS running_avg,
6  SUM(daily_sales) OVER (
7    ORDER BY sale_date 
8    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
9  ) AS last_7_days_total
10FROM daily_sales;

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

advanced

Answer

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

Example Code

SQL

1SELECT 
2  sale_date,
3  daily_sales,
4  SUM(daily_sales) OVER (ORDER BY sale_date) AS running_total,
5  ROUND(AVG(daily_sales) OVER (ORDER BY sale_date), 2) AS running_avg,
6  SUM(daily_sales) OVER (
7    ORDER BY sale_date 
8    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
9  ) AS last_7_days_total
10FROM daily_sales;

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

advanced

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.

Example Code

SQL

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

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

advanced

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.

Example Code

SQL

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

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

advanced

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.

Example Code

SQL

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

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

advanced

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.

Example Code

SQL

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

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

advanced

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.

Example Code

SQL

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

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

advanced

Answer

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

Example Code

SQL

1WITH first_purchase AS (
2  SELECT customer_id, MIN(DATE_TRUNC('month', order_date)) AS cohort_month
3  FROM orders
4  GROUP BY customer_id
5),
6retention AS (
7  SELECT 
8    f.cohort_month,
9    DATE_TRUNC('month', o.order_date) AS active_month,
10    COUNT(DISTINCT o.customer_id) AS retained_customers
11  FROM first_purchase f
12  JOIN orders o ON f.customer_id = o.customer_id
13  GROUP BY f.cohort_month, DATE_TRUNC('month', o.order_date)
14)
15SELECT 
16  cohort_month,
17  active_month,
18  retained_customers,
19  ROUND(retained_customers * 100.0 / FIRST_VALUE(retained_customers) 
20    OVER (PARTITION BY cohort_month ORDER BY active_month), 2) AS retention_pct
21FROM retention
22ORDER BY cohort_month, active_month;

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

advanced

Answer

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

Example Code

SQL

1WITH first_purchase AS (
2  SELECT customer_id, MIN(DATE_TRUNC('month', order_date)) AS cohort_month
3  FROM orders
4  GROUP BY customer_id
5),
6retention AS (
7  SELECT 
8    f.cohort_month,
9    DATE_TRUNC('month', o.order_date) AS active_month,
10    COUNT(DISTINCT o.customer_id) AS retained_customers
11  FROM first_purchase f
12  JOIN orders o ON f.customer_id = o.customer_id
13  GROUP BY f.cohort_month, DATE_TRUNC('month', o.order_date)
14)
15SELECT 
16  cohort_month,
17  active_month,
18  retained_customers,
19  ROUND(retained_customers * 100.0 / FIRST_VALUE(retained_customers) 
20    OVER (PARTITION BY cohort_month ORDER BY active_month), 2) AS retention_pct
21FROM retention
22ORDER BY cohort_month, active_month;

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

advanced

Answer

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

Example Code

SQL

1WITH first_purchase AS (
2  SELECT customer_id, MIN(DATE_TRUNC('month', order_date)) AS cohort_month
3  FROM orders
4  GROUP BY customer_id
5),
6retention AS (
7  SELECT 
8    f.cohort_month,
9    DATE_TRUNC('month', o.order_date) AS active_month,
10    COUNT(DISTINCT o.customer_id) AS retained_customers
11  FROM first_purchase f
12  JOIN orders o ON f.customer_id = o.customer_id
13  GROUP BY f.cohort_month, DATE_TRUNC('month', o.order_date)
14)
15SELECT 
16  cohort_month,
17  active_month,
18  retained_customers,
19  ROUND(retained_customers * 100.0 / FIRST_VALUE(retained_customers) 
20    OVER (PARTITION BY cohort_month ORDER BY active_month), 2) AS retention_pct
21FROM retention
22ORDER BY cohort_month, active_month;

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

advanced

Answer

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

Example Code

SQL

1WITH first_purchase AS (
2  SELECT customer_id, MIN(DATE_TRUNC('month', order_date)) AS cohort_month
3  FROM orders
4  GROUP BY customer_id
5),
6retention AS (
7  SELECT 
8    f.cohort_month,
9    DATE_TRUNC('month', o.order_date) AS active_month,
10    COUNT(DISTINCT o.customer_id) AS retained_customers
11  FROM first_purchase f
12  JOIN orders o ON f.customer_id = o.customer_id
13  GROUP BY f.cohort_month, DATE_TRUNC('month', o.order_date)
14)
15SELECT 
16  cohort_month,
17  active_month,
18  retained_customers,
19  ROUND(retained_customers * 100.0 / FIRST_VALUE(retained_customers) 
20    OVER (PARTITION BY cohort_month ORDER BY active_month), 2) AS retention_pct
21FROM retention
22ORDER BY cohort_month, active_month;

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

advanced

Answer

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

Example Code

SQL

1WITH first_purchase AS (
2  SELECT customer_id, MIN(DATE_TRUNC('month', order_date)) AS cohort_month
3  FROM orders
4  GROUP BY customer_id
5),
6retention AS (
7  SELECT 
8    f.cohort_month,
9    DATE_TRUNC('month', o.order_date) AS active_month,
10    COUNT(DISTINCT o.customer_id) AS retained_customers
11  FROM first_purchase f
12  JOIN orders o ON f.customer_id = o.customer_id
13  GROUP BY f.cohort_month, DATE_TRUNC('month', o.order_date)
14)
15SELECT 
16  cohort_month,
17  active_month,
18  retained_customers,
19  ROUND(retained_customers * 100.0 / FIRST_VALUE(retained_customers) 
20    OVER (PARTITION BY cohort_month ORDER BY active_month), 2) AS retention_pct
21FROM retention
22ORDER BY cohort_month, active_month;

Interview Tips

Practice writing queries without an IDE to simulate whiteboard interviews
Explain your thought process as you solve problems
Ask clarifying questions about edge cases
Consider query performance and scalability

Ready for your interview?

Practice with our interactive SQL sandbox and get instant feedback.