#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Analytics

SQL Query Interview Questions for Data Analyst Roles

Data analyst SQL rounds tend to test the same few skills under different stories: filtering and grouping, joins that keep or drop rows, ranking, running totals, period-over-period change and cleaning duplicates. The eight questions below cover those patterns on one small dataset. Every answer has been run on MySQL 8.0, with the exact output shown. They are practice questions written for this page, not a record of any company's actual interview.

The dataset

CREATE TABLE customers (customer_id INT PRIMARY KEY, name VARCHAR(20), city VARCHAR(15), signup_date DATE);
CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT, order_date DATE, amount DECIMAL(10,2));

INSERT INTO customers VALUES
(1,'Aarav','Chennai','2026-01-10'), (2,'Bhumi','Chennai','2026-02-03'),
(3,'Chetan','Kolkata','2026-02-20'), (4,'Diya','Kolkata','2026-03-15'),
(5,'Eshan','Chennai','2026-04-01');
INSERT INTO orders VALUES
(101,1,'2026-01-12',1200), (102,1,'2026-02-15',800), (103,2,'2026-02-05',2500),
(104,3,'2026-03-01',900),  (105,1,'2026-03-20',1500), (106,3,'2026-03-25',900),
(107,4,'2026-04-02',3100), (108,2,'2026-04-10',700), (109,4,'2026-04-28',600),
(110,2,'2026-04-10',700);

Try each question yourself before reading the answer. Saying your approach out loud first ("I'll aggregate per customer, then rank within city") is a habit worth practising for the real round.

Q1. Find the second-highest order amount.

SELECT MAX(amount) AS second_highest
FROM orders
WHERE amount < (SELECT MAX(amount) FROM orders);
second_highest
2500.00

An alternative is SELECT DISTINCT amount FROM orders ORDER BY amount DESC LIMIT 1 OFFSET 1;, which also returns 2500.00. Mention DISTINCT: without it, a tie for the highest value would return the highest again. Expect the follow-up "what if there is no second value?" The MAX version returns NULL; the LIMIT version returns no row.

Q2. List customers who have never ordered.

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;
name
Eshan

This is the anti-join pattern. WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id) is equally good. Avoid NOT IN on a column that can contain NULL, because a single NULL makes it return nothing. See MySQL joins explained.

Q3. Who is the top-spending customer in each city?

SELECT city, name, spend
FROM (
  SELECT c.city, c.name, SUM(o.amount) AS spend,
         DENSE_RANK() OVER (PARTITION BY c.city ORDER BY SUM(o.amount) DESC) AS rnk
  FROM customers c
  JOIN orders o ON o.customer_id = c.customer_id
  GROUP BY c.city, c.name
) t
WHERE rnk = 1
ORDER BY city;
citynamespend
ChennaiBhumi3900.00
KolkataDiya3700.00

Aggregate first, then rank within each city. DENSE_RANK keeps both customers if two tie for first; ROW_NUMBER would pick one arbitrarily. Being able to explain that choice matters as much as the query. See RANK vs DENSE_RANK vs ROW_NUMBER.

Q4. Show a running total of revenue for April.

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date, order_id) AS running_total
FROM orders
WHERE order_date >= '2026-04-01'
ORDER BY order_date, order_id;
order_dateamountrunning_total
2026-04-023100.003100.00
2026-04-10700.003800.00
2026-04-10700.004500.00
2026-04-28600.005100.00

The order_id tie-breaker matters. With ORDER BY order_date alone, the default window frame treats both 10 April rows as peers, and both would show 4500.00.

Q5. Find duplicate orders, then remove the extras.

SELECT customer_id, order_date, amount, COUNT(*) AS copies, GROUP_CONCAT(order_id) AS order_ids
FROM orders
GROUP BY customer_id, order_date, amount
HAVING COUNT(*) > 1;
customer_idorder_dateamountcopiesorder_ids
22026-04-10700.002108,110

To find the rows to delete while keeping the earliest copy:

SELECT order_id
FROM (
  SELECT order_id,
         ROW_NUMBER() OVER (PARTITION BY customer_id, order_date, amount ORDER BY order_id) AS rn
  FROM orders
) t
WHERE rn > 1;
order_id
110

Say out loud that you would check with the business whether two equal orders on one day are really duplicates. A customer can genuinely buy the same thing twice.

Q6. Calculate month-on-month revenue growth.

WITH m AS (
  SELECT DATE_FORMAT(order_date, '%Y-%m') AS mon, SUM(amount) AS rev
  FROM orders GROUP BY mon
)
SELECT mon, rev,
       ROUND(100 * (rev - LAG(rev) OVER (ORDER BY mon)) / LAG(rev) OVER (ORDER BY mon), 1) AS mom_pct
FROM m
ORDER BY mon;
monrevmom_pct
2026-011200.00NULL
2026-023300.00175.0
2026-033300.000.0
2026-045100.0054.5

The first month has nothing to compare with, so it is NULL, not 0. A month with no orders would be missing entirely. In a real report, you would join to a calendar table so that gaps show as zero. More on LAG in the window functions tutorial.

Q7. Show each customer's first order.

SELECT c.name, o.order_date, o.amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE (o.customer_id, o.order_date) IN (
  SELECT customer_id, MIN(order_date) FROM orders GROUP BY customer_id
)
ORDER BY o.order_date;
nameorder_dateamount
Aarav2026-01-121200.00
Bhumi2026-02-052500.00
Chetan2026-03-01900.00
Diya2026-04-023100.00

If a customer had two orders on their first day, this returns both. ROW_NUMBER() ... WHERE rn = 1 returns exactly one. Know both, and say which behaviour the question needs.

Q8. What share of signed-up customers are repeat buyers?

SELECT COUNT(*) AS customers,
       SUM(order_count >= 2) AS repeat_customers,
       ROUND(100 * SUM(order_count >= 2) / COUNT(*), 1) AS repeat_pct
FROM (
  SELECT c.customer_id, COUNT(o.order_id) AS order_count
  FROM customers c
  LEFT JOIN orders o ON o.customer_id = c.customer_id
  GROUP BY c.customer_id
) t;
customersrepeat_customersrepeat_pct
5480.0

The denominator is the trap. Starting from orders would leave out Eshan and report every customer as a repeat buyer. Use COUNT(o.order_id), not COUNT(*), so that a customer with no orders counts 0, not 1.

Before the interview

Become a Job-Ready Data Analyst

Master MySQL, Advanced Excel, Power BI, Tableau and Python with Uncodemy's Data Analytics Training in Noida — live projects, expert mentors and Dedicated Placement Support.

Enroll in Data Analytics Course →