SQL ইন্টারভিউ ও সমস্যা সমাধানের অনুশীলন

FROM / JOIN  →  WHERE  →  GROUP BY  →  HAVING  →  SELECT (উইন্ডো ফাংশন এখানে)  →  DISTINCT  →  ORDER BY  →  LIMIT

SELECT customer_id, name, city FROM customers WHERE city <> 'Dhaka' ORDER BY customer_id;

SELECT customer_id, name, city
FROM customers
WHERE city <> 'Dhaka' OR city IS NULL
ORDER BY customer_id;

+-------------+---------------+------------+
| customer_id | name          | city       |
+-------------+---------------+------------+
|           2 | Tanvir Ahmed  | Chattogram |
|           4 | Rafiq Islam   | Sylhet     |
|           6 | Imran Hossain | Khulna     |
|           8 | Karim Uddin   | Rajshahi   |
+-------------+---------------+------------+
+-------------+-----------------+------------+
| customer_id | name            | city       |
+-------------+-----------------+------------+
|           2 | Tanvir Ahmed    | Chattogram |
|           4 | Rafiq Islam     | Sylhet     |
|           5 | Sadia Chowdhury | NULL       |
|           6 | Imran Hossain   | Khulna     |
|           8 | Karim Uddin     | Rajshahi   |
+-------------+-----------------+------------+

SELECT product_id, name, price, stock
FROM products
WHERE price < 3000
  AND (stock > 0 OR stock IS NULL)      -- NULL স্টক = ডিজিটাল প্রোডাক্ট, কখনো ফুরায় না
ORDER BY price, product_id;

+------------+---------------------------+-------+-------+
| product_id | name                      | price | stock |
+------------+---------------------------+-------+-------+
|          4 | Wireless Mouse            |   900 |    60 |
|         11 | Gift Card                 |  1000 | NULL  |
|          1 | Python Crash Course       |  1200 |    40 |
|          8 | Laptop Stand              |  1500 |    25 |
|          2 | Hands-On Machine Learning |  2500 |    15 |
+------------+---------------------------+-------+-------+

SELECT c.name AS category,
       COALESCE(SUM(CASE WHEN o.status <> 'cancelled'
                         THEN oi.quantity * oi.unit_price END), 0) AS revenue
FROM categories AS c
LEFT JOIN products AS p ON p.category_id = c.category_id
LEFT JOIN order_items AS oi ON oi.product_id = p.product_id
LEFT JOIN orders AS o ON o.order_id = oi.order_id
GROUP BY c.category_id, c.name
ORDER BY revenue DESC, category;

+-------------+---------+
| category    | revenue |
+-------------+---------+
| Courses     |   36000 |
| Electronics |   31550 |
| Accessories |    8900 |
| Books       |    8600 |
| Furniture   |       0 |
+-------------+---------+

SELECT c.customer_id, c.name
FROM customers AS c
WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id)
ORDER BY c.customer_id;

+-------------+-------------+
| customer_id | name        |
+-------------+-------------+
|           8 | Karim Uddin |
+-------------+-------------+

-- ভুল: orders-এর সাথে দুটো "many" টেবিল জয়েন করলে সারি গুণ হয়ে যায়
SELECT o.order_id,
       SUM(oi.quantity * oi.unit_price) AS order_total,
       COALESCE(SUM(p.amount), 0) AS paid
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
LEFT JOIN payments AS p ON p.order_id = o.order_id
GROUP BY o.order_id
HAVING order_total <> paid
ORDER BY o.order_id;

+----------+-------------+-------+
| order_id | order_total | paid  |
+----------+-------------+-------+
|        1 |        2100 |  4200 |
|        4 |        4000 |  8000 |
|        5 |       18500 |     0 |
|        6 |       30000 | 15000 |
|        7 |        4000 |  8000 |
|        9 |        4950 |  9900 |
|       11 |        5500 | 11000 |
|       13 |       15000 |     0 |
|       14 |        3100 |  6200 |
+----------+-------------+-------+

WITH totals AS (
    SELECT order_id, SUM(quantity * unit_price) AS order_total
    FROM order_items GROUP BY order_id
),
paid AS (
    SELECT order_id, SUM(amount) AS paid
    FROM payments GROUP BY order_id
)
SELECT o.order_id, o.status, t.order_total, COALESCE(p.paid, 0) AS paid
FROM orders AS o
JOIN totals AS t ON t.order_id = o.order_id
LEFT JOIN paid AS p ON p.order_id = o.order_id
WHERE t.order_total <> COALESCE(p.paid, 0)
ORDER BY o.order_id;

+----------+-----------+-------------+------+
| order_id | status    | order_total | paid |
+----------+-----------+-------------+------+
|        5 | cancelled |       18500 |    0 |
|       13 | pending   |       15000 |    0 |
+----------+-----------+-------------+------+

SELECT p.name, p.category_id, p.price
FROM products AS p
WHERE p.price > (SELECT AVG(p2.price) FROM products AS p2
                 WHERE p2.category_id = p.category_id)
ORDER BY p.category_id, p.price DESC;

+---------------------------+-------------+-------+
| name                      | category_id | price |
+---------------------------+-------------+-------+
| Hands-On Machine Learning |           1 |  2500 |
| 27-inch Monitor           |           2 | 18500 |
| AI Engineering Bootcamp   |           3 | 15000 |
| USB-C Hub                 |           4 |  2200 |
+---------------------------+-------------+-------+

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

WITH ranked AS (
    SELECT name, department, salary,
           DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT department, name, salary
FROM ranked
WHERE rnk = 2
ORDER BY department, name;

+----------------+
| second_highest |
+----------------+
|         180000 |
+----------------+
+-------------+--------------+--------+
| department  | name         | salary |
+-------------+--------------+--------+
| Data        | Priya Sen    | 110000 |
| Engineering | Nusrat Jahan | 120000 |
| Engineering | Zahid Hasan  | 120000 |
+-------------+--------------+--------+

WITH product_revenue AS (
    SELECT p.category_id, p.name, SUM(oi.quantity * oi.unit_price) AS revenue
    FROM products AS p
    JOIN order_items AS oi ON oi.product_id = p.product_id
    JOIN orders AS o ON o.order_id = oi.order_id AND o.status <> 'cancelled'
    GROUP BY p.product_id, p.category_id, p.name
),
ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY revenue DESC, name) AS rn
    FROM product_revenue
)
SELECT category_id, name, revenue, rn
FROM ranked
WHERE rn <= 2
ORDER BY category_id, rn;

+-------------+---------------------------+---------+----+
| category_id | name                      | revenue | rn |
+-------------+---------------------------+---------+----+
|           1 | Hands-On Machine Learning |    5000 |  1 |
|           1 | Python Crash Course       |    3600 |  2 |
|           2 | 27-inch Monitor           |   18500 |  1 |
|           2 | Mechanical Keyboard       |    8550 |  2 |
|           3 | AI Engineering Bootcamp   |   30000 |  1 |
|           3 | SQL Masterclass           |    6000 |  2 |
|           4 | Laptop Stand              |    4500 |  1 |
|           4 | USB-C Hub                 |    4400 |  2 |
+-------------+---------------------------+---------+----+

WITH monthly AS (
    SELECT strftime('%Y-%m', o.order_date) AS month,
           SUM(oi.quantity * oi.unit_price) AS revenue
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    WHERE o.status <> 'cancelled'
    GROUP BY month
)
SELECT month, revenue,
       SUM(revenue) OVER (ORDER BY month) AS running_total,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / LAG(revenue) OVER (ORDER BY month), 1) AS mom_pct
FROM monthly
ORDER BY month;

+---------+---------+---------------+---------+
| month   | revenue | running_total | mom_pct |
+---------+---------+---------------+---------+
| 2026-01 |    6600 |          6600 | NULL    |
| 2026-02 |    7000 |         13600 |     6.1 |
| 2026-03 |   21400 |         35000 |   205.7 |
| 2026-04 |   23450 |         58450 |     9.6 |
| 2026-05 |    8500 |         66950 |   -63.8 |
| 2026-06 |   18100 |         85050 |   112.9 |
+---------+---------+---------------+---------+

CREATE TABLE logins (user_id INTEGER, login_date TEXT);
INSERT INTO logins VALUES
    (1, '2026-06-01'), (1, '2026-06-02'), (1, '2026-06-03'), (1, '2026-06-05'), (1, '2026-06-06'),
    (2, '2026-06-01'), (2, '2026-06-03'), (2, '2026-06-04'), (2, '2026-06-05'), (2, '2026-06-06'),
    (2, '2026-06-06'), (3, '2026-06-02');

WITH days AS (
    SELECT DISTINCT user_id, login_date FROM logins
),
islands AS (
    SELECT user_id, login_date,
           julianday(login_date)
             - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS grp
    FROM days
),
streaks AS (
    SELECT user_id, MIN(login_date) AS start_day, MAX(login_date) AS end_day, COUNT(*) AS length
    FROM islands
    GROUP BY user_id, grp
)
SELECT user_id, start_day, end_day, length
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY length DESC, start_day) AS rn
      FROM streaks)
WHERE rn = 1
ORDER BY user_id;

+---------+------------+------------+--------+
| user_id | start_day  | end_day    | length |
+---------+------------+------------+--------+
|       1 | 2026-06-01 | 2026-06-03 |      3 |
|       2 | 2026-06-03 | 2026-06-06 |      4 |
|       3 | 2026-06-02 | 2026-06-02 |      1 |
+---------+------------+------------+--------+

WITH valid AS (
    SELECT customer_id, order_date FROM orders WHERE status <> 'cancelled'
),
firsts AS (
    SELECT customer_id, MIN(order_date) AS first_date FROM valid GROUP BY customer_id
)
SELECT strftime('%Y-%m', f.first_date) AS cohort,
       COUNT(*) AS customers,
       SUM(EXISTS (SELECT 1 FROM valid AS v
                   WHERE v.customer_id = f.customer_id
                     AND strftime('%Y-%m', v.order_date) > strftime('%Y-%m', f.first_date))) AS came_back,
       ROUND(100.0 * SUM(EXISTS (SELECT 1 FROM valid AS v
                   WHERE v.customer_id = f.customer_id
                     AND strftime('%Y-%m', v.order_date) > strftime('%Y-%m', f.first_date))) / COUNT(*), 0) AS pct
FROM firsts AS f
GROUP BY cohort
ORDER BY cohort;

+---------+-----------+-----------+-------+
| cohort  | customers | came_back | pct   |
+---------+-----------+-----------+-------+
| 2026-01 |         2 |         2 | 100.0 |
| 2026-02 |         1 |         1 | 100.0 |
| 2026-03 |         1 |         0 |   0.0 |
| 2026-04 |         1 |         1 | 100.0 |
| 2026-05 |         1 |         0 |   0.0 |
+---------+-----------+-----------+-------+

INSERT INTO customers (customer_id, name, email, city, joined_on) VALUES
    (9, 'Nadia R.', ' NADIA@example.com', 'Dhaka', '2026-06-20');

SELECT LOWER(TRIM(email)) AS clean_email, COUNT(*) AS n, GROUP_CONCAT(customer_id ORDER BY customer_id) AS ids
FROM customers
GROUP BY clean_email
HAVING COUNT(*) > 1;

WITH ranked AS (
    SELECT customer_id,
           ROW_NUMBER() OVER (PARTITION BY LOWER(TRIM(email)) ORDER BY joined_on, customer_id) AS rn
    FROM customers
)
SELECT customer_id AS duplicate_to_remove FROM ranked WHERE rn > 1;

+-------------------+---+-----+
| clean_email       | n | ids |
+-------------------+---+-----+
| nadia@example.com | 2 | 1,9 |
+-------------------+---+-----+
+---------------------+
| duplicate_to_remove |
+---------------------+
|                   9 |
+---------------------+

SELECT c.customer_id, COUNT(*) AS delivered_orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.status = 'delivered'
GROUP BY c.customer_id
ORDER BY c.customer_id;

+-------------+------------------+
| customer_id | delivered_orders |
+-------------+------------------+
|           1 |                3 |
|           2 |                2 |
|           3 |                3 |
|           5 |                1 |
|           6 |                1 |
|           7 |                1 |
+-------------+------------------+

SELECT c.customer_id, COUNT(o.order_id) AS delivered_orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id AND o.status = 'delivered'
GROUP BY c.customer_id
ORDER BY c.customer_id;

+-------------+------------------+
| customer_id | delivered_orders |
+-------------+------------------+
|           1 |                3 |
|           2 |                2 |
|           3 |                3 |
|           4 |                0 |
|           5 |                1 |
|           6 |                1 |
|           7 |                1 |
|           8 |                0 |
|           9 |                0 |
+-------------+------------------+

CREATE INDEX idx_orders_date ON orders (order_date);

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM orders WHERE strftime('%Y-%m', order_date) = '2026-03';

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM orders WHERE order_date >= '2026-03-01' AND order_date < '2026-04-01';

+----+--------+---------+--------------------------------------------------+
| id | parent | notused | detail                                           |
+----+--------+---------+--------------------------------------------------+
|  3 |      0 |       0 | SCAN orders USING COVERING INDEX idx_orders_date |
+----+--------+---------+--------------------------------------------------+
+----+--------+---------+------------------------------------------------------------------------------------+
| id | parent | notused | detail                                                                             |
+----+--------+---------+------------------------------------------------------------------------------------+
|  3 |      0 |       0 | SEARCH orders USING COVERING INDEX idx_orders_date (order_date>? AND order_date<?) |
+----+--------+---------+------------------------------------------------------------------------------------+

-- ১.
SELECT method, COUNT(*) AS payments, SUM(amount) AS total
FROM payments
GROUP BY method
ORDER BY total DESC, method;

-- ২.
SELECT customer_id,
       CAST(julianday(MAX(order_date)) - julianday(MIN(order_date)) AS INTEGER) AS days_between
FROM orders
WHERE status <> 'cancelled'
GROUP BY customer_id
ORDER BY customer_id;

-- ৩.
WITH order_totals AS (
    SELECT o.order_id, o.customer_id, SUM(oi.quantity * oi.unit_price) AS total
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    WHERE o.status <> 'cancelled'
    GROUP BY o.order_id, o.customer_id
)
SELECT customer_id, order_id, total,
       ROUND(100.0 * total / SUM(total) OVER (PARTITION BY customer_id), 1) AS pct_of_customer
FROM order_totals
ORDER BY customer_id, order_id;