Menu
CoddyTech
Start learning
Learn SQL step by stepOnline SQL compiler

SQL Interview Questions for Data Analysts

Reporting queries: dates, percentages, pivots, medians, retention and streaks.

By Kevin Spektor, Co-founder & CTO

10 questions

  1. How do you group sales by month?

    Turn the date into a month value and group by it.

    SQL
    CREATE TABLE orders (id INTEGER, order_date TEXT, amount INTEGER);
INSERT INTO orders VALUES
  (1, '2026-01-05', 200), (2, '2026-01-20', 300),
  (3, '2026-02-11', 150), (4, '2026-03-02', 400), (5, '2026-03-28', 100);
SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
GROUP BY month
ORDER BY month;

    The function differs by database: strftime('%Y-%m', d) in SQLite, DATE_FORMAT(d, '%Y-%m') in MySQL, DATE_TRUNC('month', d) in PostgreSQL. A month with no orders is missing; LEFT JOIN from a calendar to show a zero. More in date and time functions.

  2. How do you calculate each row's percentage of the total?

    Divide by a window total: SUM(amount) OVER () repeats the grand total on every row, so no subquery is needed.

    SQL
    CREATE TABLE region_sales (region TEXT, amount INTEGER);
INSERT INTO region_sales VALUES ('North', 450), ('South', 300), ('East', 150), ('West', 100);
SELECT region, amount,
       ROUND(100.0 * amount / SUM(amount) OVER (), 1) AS pct_of_total
FROM region_sales
ORDER BY amount DESC;

    The total is 1000, so North is 45.0. Write 100.0, not 100, or SQLite, PostgreSQL and SQL Server do integer division.

  3. How do you pivot rows into columns in SQL?

    Group by the row label and write one conditional aggregate per output column.

    SQL
    CREATE TABLE sales (region TEXT, quarter TEXT, amount INTEGER);
INSERT INTO sales VALUES
  ('North', 'Q1', 100), ('North', 'Q2', 150), ('South', 'Q1', 80),
  ('South', 'Q2', 120), ('North', 'Q1', 50);
SELECT region,
       SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS q1,
       SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS q2
FROM sales
GROUP BY region
ORDER BY region;

    North's two Q1 rows add up to 150, and the query works in every database. SQL Server's PIVOT and PostgreSQL's crosstab also need the columns listed in advance.

  4. How do you calculate the median in SQL?

    Number the sorted rows, count them, and average the middle one or two.

    SQL
    CREATE TABLE orders (id INTEGER, amount INTEGER);
INSERT INTO orders VALUES (1, 3), (2, 1), (3, 4), (4, 1), (5, 5), (6, 9);
WITH ordered AS (
  SELECT amount,
         ROW_NUMBER() OVER (ORDER BY amount) AS rn,
         COUNT(*) OVER () AS n
  FROM orders
)
SELECT AVG(amount) AS median
FROM ordered
WHERE rn IN ((n + 1) / 2, (n + 2) / 2);

    Sorted, the values are 1, 1, 3, 4, 5, 9, so the median is 3.5. Integer division picks the positions: 3 and 4 for n = 6, 3 and 3 for n = 5 (MySQL needs FLOOR). PostgreSQL and Oracle have PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount); MySQL has no built-in, and SQLite's median() needs its percentile extension compiled in.

  5. How do you calculate day 1 retention from signups and logins?

    For each signup cohort, count the users who logged in the day after signing up and divide by the cohort size.

    SQL
    CREATE TABLE users (id INTEGER, signup_date TEXT);
INSERT INTO users VALUES (1, '2026-03-01'), (2, '2026-03-01'), (3, '2026-03-02'), (4, '2026-03-02');
CREATE TABLE logins (user_id INTEGER, login_date TEXT);
INSERT INTO logins VALUES
  (1, '2026-03-02'), (1, '2026-03-02'), (2, '2026-03-05'), (3, '2026-03-03'), (4, '2026-03-03');
SELECT u.signup_date,
       COUNT(DISTINCT u.id) AS signups,
       COUNT(DISTINCT l.user_id) AS returned_next_day,
       ROUND(100.0 * COUNT(DISTINCT l.user_id) / COUNT(DISTINCT u.id), 1) AS day1_pct
FROM users u
LEFT JOIN logins l
  ON l.user_id = u.id AND l.login_date = date(u.signup_date, '+1 day')
GROUP BY u.signup_date
ORDER BY u.signup_date;

    March 1 retained one of two users (50.0), March 2 both (100.0). The date condition sits in ON so users who did not return stay in the denominator, and COUNT(DISTINCT ...) stops repeat logins from counting twice.

  6. How do you find the longest streak of consecutive login days per user?

    Subtract the row number from the date (gaps and islands): within a run of consecutive days both rise by one, so the difference stays constant and identifies the streak.

    SQL
    CREATE TABLE logins (user_id INTEGER, login_date TEXT);
INSERT INTO logins VALUES
  (1, '2026-03-01'), (1, '2026-03-02'), (1, '2026-03-03'),
  (1, '2026-03-05'), (1, '2026-03-06'), (2, '2026-03-01'), (2, '2026-03-03');
WITH days AS (
  SELECT DISTINCT user_id, login_date FROM logins
), grouped AS (
  SELECT user_id,
         julianday(login_date) - ROW_NUMBER() OVER (
           PARTITION BY user_id ORDER BY login_date
         ) AS streak_key
  FROM days
)
SELECT user_id, MAX(streak_len) AS longest_streak
FROM (SELECT user_id, COUNT(*) AS streak_len FROM grouped GROUP BY user_id, streak_key) AS s
GROUP BY user_id
ORDER BY user_id;

    User 1's longest streak is 3 days (March 1 to 3); user 2's is 1. The DISTINCT matters because two logins on one day would break the arithmetic.

    Practice it: Longest Consecutive Sequence, Summary Ranges

  7. How do you group values into buckets, such as age groups?

    Label each row with a CASE expression and group by the label.

    SQL
    CREATE TABLE customers (name TEXT, age INTEGER);
INSERT INTO customers VALUES
  ('Asha', 19), ('Ravi', 24), ('Meera', 31), ('John', 45), ('Priya', 28), ('Karan', 62);
SELECT CASE
         WHEN age < 25 THEN '18-24'
         WHEN age < 35 THEN '25-34'
         WHEN age < 50 THEN '35-49'
         ELSE '50+'
       END AS age_group,
       COUNT(*) AS customers
FROM customers
GROUP BY age_group
ORDER BY age_group;

    CASE stops at the first true branch, so each branch needs only an upper bound. Grouping by the alias works in MySQL, PostgreSQL and SQLite; SQL Server needs the whole CASE in GROUP BY.

  8. How do you avoid a division by zero error in SQL?

    Wrap the divisor in NULLIF(divisor, 0): it returns NULL when the divisor is 0, and dividing by NULL gives NULL instead of an error. Add COALESCE(..., 0) if the report should show 0.

    SQL
    CREATE TABLE campaigns (name TEXT, clicks INTEGER, signups INTEGER);
INSERT INTO campaigns VALUES ('Search', 200, 10), ('Social', 0, 0), ('Email', 40, 5);
SELECT name,
       ROUND(100.0 * signups / NULLIF(clicks, 0), 1) AS conversion_pct,
       COALESCE(ROUND(100.0 * signups / NULLIF(clicks, 0), 1), 0) AS shown_as_zero
FROM campaigns
ORDER BY name;

    Social, with no clicks, gets NULL and then 0, and Email shows 12.5. PostgreSQL and SQL Server raise an error on division by zero, while MySQL and SQLite return NULL, so NULLIF makes the query behave the same everywhere.

  9. How do you calculate month-over-month growth in SQL?

    Aggregate to one row per month first, then compare each month with the one before it using LAG: (revenue - previous) / previous * 100.

    SQL
    CREATE TABLE orders (order_date TEXT, amount INTEGER);
INSERT INTO orders VALUES
  ('2026-01-05', 400), ('2026-01-20', 600),
  ('2026-02-11', 1200), ('2026-03-02', 900);
WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month, SUM(amount) AS revenue
  FROM orders
  GROUP BY month
)
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS growth_pct
FROM monthly
ORDER BY month;

    January has 1000 and no previous month (NULL), February grows 20.0% and March falls 25.0%. NULLIF guards against a month with zero revenue. A month with no orders has no row, so LAG would silently compare with an older month; join to a calendar first if gaps are possible.

  10. How do you show days with no sales as zero in a report?

    Generate every date in a calendar, LEFT JOIN the data to it, and turn the missing sums into 0 with COALESCE. A plain GROUP BY on the sales table can only return days that have rows.

    SQL
    CREATE TABLE orders (order_date TEXT, amount INTEGER);
INSERT INTO orders VALUES
  ('2026-03-01', 200), ('2026-03-01', 50), ('2026-03-03', 120), ('2026-03-05', 80);
WITH RECURSIVE calendar(day) AS (
  SELECT '2026-03-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM calendar WHERE day < '2026-03-05'
)
SELECT c.day, COALESCE(SUM(o.amount), 0) AS revenue
FROM calendar c
LEFT JOIN orders o ON o.order_date = c.day
GROUP BY c.day
ORDER BY c.day;

    March 2 and March 4 appear with 0. PostgreSQL generates the dates with generate_series('2026-03-01'::date, '2026-03-05', interval '1 day'), and in production a permanent calendar table is simpler. More on recursive CTEs.

Coddy programming languages illustration

Learn to code with Coddy

Get started