ভূমিকা

এই পোস্টে বাস্তব ইন্টারভিউতে আসা ১০টি SQL কুয়েরি সমস্যা এবং তাদের step-by-step সমাধান বাংলায় দেওয়া হয়েছে। প্রতিটি সমস্যায় একটি scenario, প্রয়োজনীয় টেবিল structure এবং সম্পূর্ণ SQL solution রয়েছে।

---

সমস্যা ১: বিভাগ অনুযায়ী সর্বোচ্চ বেতনের কর্মী খুঁজুন

সমস্যা: employees টেবিল থেকে প্রতিটি বিভাগের সর্বোচ্চ বেতনপ্রাপ্ত কর্মীর নাম, বিভাগ এবং বেতন বের করুন।

টেবিল:

employees (emp_id, name, dept_id, salary)
departments (dept_id, dept_name)

সমাধান:

-- পদ্ধতি ১: Subquery দিয়ে
SELECT e.name, d.dept_name, e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary = (
    SELECT MAX(salary)
    FROM employees e2
    WHERE e2.dept_id = e.dept_id
);

-- পদ্ধতি ২: Window Function দিয়ে (আরও efficient)
SELECT name, dept_name, salary
FROM (
    SELECT
        e.name,
        d.dept_name,
        e.salary,
        RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rnk
    FROM employees e
    JOIN departments d ON e.dept_id = d.dept_id
) ranked
WHERE rnk = 1;

ব্যাখ্যা: পদ্ধতি ২ বেশি efficient কারণ এটি একবার টেবিল scan করে RANK() দিয়ে প্রতিটি বিভাগের শীর্ষ কর্মী বের করে।

---

সমস্যা ২: প্রতিটি মাসে মোট বিক্রয় পরিমাণ বের করুন

সমস্যা: orders টেবিল থেকে বছর ও মাস অনুযায়ী মোট বিক্রয় (total_amount) এবং অর্ডারের সংখ্যা বের করুন, নতুন থেকে পুরনো ক্রমে সাজান।

টেবিল:

orders (order_id, customer_id, order_date, total_amount, status)

সমাধান:

SELECT
    YEAR(order_date)  AS বছর,
    MONTH(order_date) AS মাস,
    COUNT(order_id)   AS মোট_অর্ডার,
    SUM(total_amount) AS মোট_বিক্রয়,
    AVG(total_amount) AS গড়_অর্ডার_মূল্য
FROM orders
WHERE status = 'completed'
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY বছর DESC, মাস DESC;

ব্যাখ্যা: GROUP BY দিয়ে বছর ও মাস ভাগ করা হয়েছে। WHERE status = 'completed' দিয়ে শুধু সম্পন্ন অর্ডার নেওয়া হয়েছে।

---

সমস্যা ৩: পরপর তিন দিন বা তার বেশি Login করা Users খুঁজুন

সমস্যা: user_logins টেবিল থেকে সেই সব user খুঁজুন যারা কমপক্ষে ৩ দিন পরপর (consecutive) login করেছেন।

টেবিল:

user_logins (user_id, login_date)  -- login_date unique per user per day

সমাধান:

এখন SQL সমাধান এবং একটি ভিজ্যুয়াল ব্যাখ্যা তৈরি করছি।

এই সমস্যাটি সমাধানের মূল কৌশল হলো Gap and Islands problem। প্রতিটি login_date থেকে ROW_NUMBER() বাদ দিলে consecutive দিনগুলো একই গ্রুপে পড়ে।

কেন এটি কাজ করে?

যদি কেউ ৩ দিন পরপর login করেন (Jan 1, 2, 3), তাহলে:

• login_date - ROW_NUMBER() = একই মান (ধ্রুবক)
• ফলে এই ৩টি row একই "island" গ্রুপে পড়বে

**সমাধান:**প্রতিটি ধাপে ক্লিক করে logic বুঝুন। নিচে সম্পূর্ণ SQL এর ব্যাখ্যা:

---

মূল কৌশল: Gap and Islands

user_logins টেবিলে প্রতিটি user-এর login_date রয়েছে। ধরুন ৩ জন user:

user_id	login_date
U1	2024-01-01
U1	2024-01-02
U1	2024-01-03
U1	2024-01-07
U2	2024-01-05
U2	2024-01-06
U3	2024-01-10
U3	2024-01-11
U3	2024-01-12

প্রতিটি user-এর login_date-এ ROW_NUMBER() যোগ করি। PARTITION BY user_id ORDER BY login_date:

user_id	login_date	rn
U1	2024-01-01	1
U1	2024-01-02	2
U1	2024-01-03	3
U1	2024-01-07	4
U2	2024-01-05	1
U2	2024-01-06	2
U3	2024-01-10	1
U3	2024-01-11	2
U3	2024-01-12	3

login_date - rn হিসাব করি। Consecutive দিনগুলোর জন্য এই মান একই থাকে — এটাই গ্রুপ key!

user_id	login_date	rn	grp (date − rn)
U1	Jan 01	1	Dec 31
U1	Jan 02	2	Dec 31
U1	Jan 03	3	Dec 31
U1	Jan 07	4	Jan 03
U2	Jan 05	1	Jan 04
U2	Jan 06	2	Jan 04
U3	Jan 10	1	Jan 09
U3	Jan 11	2	Jan 09
U3	Jan 12	3	Jan 09

এখন GROUP BY user_id, grp করে COUNT(*) ≥ 3 দিয়ে ফিল্টার করি।

user_id	grp	streak	✓ ≥3?
U1	Dec 31	3	✓ হ্যাঁ
U1	Jan 03	1	✗ না
U2	Jan 04	2	✗ না
U3	Jan 09	3	✓ হ্যাঁ

কমপক্ষে ৩ দিন বা তার বেশি একটানা (consecutive) লগইন করা ইউজাররা হলেন: U1 এবং U3।

-- ধাপ 1: প্রতিটি user-এর login_date-এ ROW_NUMBER() যোগ করি। 
WITH numbered AS (
  SELECT
    user_id,
    login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY login_date
    ) AS rn
  FROM user_logins
),

-- ধাপ ২: Consecutive গ্রুপ চিহ্নিত করা
grouped AS (
  SELECT
    user_id,
    login_date,
    login_date - INTERVAL (rn - 1) DAY AS grp
  FROM numbered
)

-- ধাপ ৩: ≥৩ দিন streak আছে এমন user বের করা
SELECT DISTINCT user_id
FROM grouped
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

---

সমস্যা ৪: Duplicate Records খুঁজুন এবং মুছুন

সমস্যা: customers টেবিলে একই email-এ duplicate rows আছে। সবচেয়ে পুরনো record রেখে বাকিগুলো delete করুন।

টেবিল:

customers (id, name, email, created_at)

সমাধান:

-- ধাপ ১: Duplicate খোঁজা
SELECT email, COUNT(*) AS সংখ্যা
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

-- ধাপ ২: Duplicate delete করা (সবচেয়ে পুরনো রেখে)
DELETE FROM customers
WHERE id NOT IN (
    SELECT MIN(id)
    FROM customers
    GROUP BY email
);

-- MySQL-এ self-reference সমস্যার জন্য alternative:
DELETE c1
FROM customers c1
INNER JOIN customers c2
ON c1.email = c2.email
AND c1.id > c2.id;

ব্যাখ্যা: MIN(id) দিয়ে প্রতিটি email-এর সবচেয়ে পুরনো record রাখা হয়। JOIN পদ্ধতি MySQL-এ subquery সমস্যা এড়াতে ব্যবহার করা হয়।

---

সমস্যা ৫: Nth সর্বোচ্চ বেতন বের করুন

সমস্যা: employees টেবিল থেকে 3rd সর্বোচ্চ বেতন বের করুন (duplicate বেতন আলাদা হিসেবে গণ্য করবেন না)।

টেবিল:

employees (emp_id, name, salary)

সমাধান:

-- পদ্ধতি ১: DENSE_RANK দিয়ে (সবচেয়ে reliable)
SELECT salary
FROM (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 3
LIMIT 1;

-- পদ্ধতি ২: Subquery দিয়ে (N = 3)
SELECT MIN(salary) AS third_highest
FROM (
    SELECT DISTINCT salary
    FROM employees
    ORDER BY salary DESC
    LIMIT 3
) top3;

-- পদ্ধতি ৩: Generic N-এর জন্য (N এর জায়গায় যেকোনো সংখ্যা দিন)
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;   -- OFFSET = N-1

ব্যাখ্যা: DENSE_RANK() সবচেয়ে ভালো কারণ এটি duplicate salary handle করে সঠিকভাবে। OFFSET 2 মানে প্রথম ২টি skip করে ৩য়টি নেবে।

---

সমস্যা ৬: Manager এবং তাদের অধীনস্থ কর্মীদের তালিকা তৈরি করুন

সমস্যা: employees টেবিলে self-referencing foreign key আছে। প্রতিটি manager-এর নাম এবং তাদের direct reportee-দের সংখ্যা বের করুন।

টেবিল:

employees (emp_id, name, salary, manager_id)  -- manager_id references emp_id

সমাধান:

-- Manager এবং reportee সংখ্যা
SELECT
    m.name        AS ম্যানেজার_নাম,
    COUNT(e.emp_id) AS সরাসরি_অধীনস্থ_সংখ্যা,
    GROUP_CONCAT(e.name ORDER BY e.name SEPARATOR ', ') AS অধীনস্থ_কর্মীরা
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
GROUP BY m.emp_id, m.name
ORDER BY সরাসরি_অধীনস্থ_সংখ্যা DESC;

-- Recursive CTE দিয়ে সম্পূর্ণ hierarchy (multi-level)
WITH RECURSIVE org_chart AS (
    -- Base case: top-level manager (manager_id IS NULL)
    SELECT emp_id, name, manager_id, 0 AS level, name AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case
    SELECT e.emp_id, e.name, e.manager_id, oc.level + 1,
           CONCAT(oc.path, ' > ', e.name)
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.emp_id
)
SELECT emp_id, name, level, path
FROM org_chart
ORDER BY path;

ব্যাখ্যা: Self-JOIN দিয়ে এক স্তরের hierarchy বের করা যায়। Multi-level hierarchy-র জন্য Recursive CTE ব্যবহার করা হয়।

---

সমস্যা ৭: Rolling/Cumulative Total বের করুন

সমস্যা: sales টেবিল থেকে প্রতিটি দিনের বিক্রয়ের পাশাপাশি সেই মাসের cumulative (চলমান মোট) বিক্রয় বের করুন।

টেবিল:

sales (sale_id, sale_date, amount, product_id)

সমাধান:

SELECT
    sale_date,
    amount                              AS দৈনিক_বিক্রয়,
    SUM(amount) OVER (
        PARTITION BY YEAR(sale_date), MONTH(sale_date)
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )                                   AS মাসিক_চলমান_মোট,
    SUM(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )                                   AS সার্বিক_চলমান_মোট,
    AVG(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    )                                   AS ৭_দিনের_গড়
FROM sales
ORDER BY sale_date;

ব্যাখ্যা: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW মানে শুরু থেকে বর্তমান row পর্যন্ত sum করে। ROWS BETWEEN 6 PRECEDING AND CURRENT ROW দিয়ে গত ৭ দিনের moving average বের করা হয়।

---

সমস্যা ৮: দুটি টেবিলের মধ্যে Missing Records খুঁজুন

সমস্যা: products টেবিলে সব পণ্য আছে, কিন্তু order_items টেবিলে শুধু বিক্রি হওয়া পণ্য আছে। যেসব পণ্য গত ৩০ দিনে একবারও বিক্রি হয়নি তাদের খুঁজুন।

টেবিল:

products (product_id, product_name, category, price)
order_items (item_id, order_id, product_id, quantity, order_date)

সমাধান:

-- পদ্ধতি ১: LEFT JOIN + NULL check (সবচেয়ে efficient)
SELECT p.product_id, p.product_name, p.category, p.price
FROM products p
LEFT JOIN order_items oi
    ON p.product_id = oi.product_id
    AND oi.order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
WHERE oi.product_id IS NULL
ORDER BY p.category, p.product_name;

-- পদ্ধতি ২: NOT EXISTS
SELECT product_id, product_name, category
FROM products p
WHERE NOT EXISTS (
    SELECT 1
    FROM order_items oi
    WHERE oi.product_id = p.product_id
    AND oi.order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
);

-- পদ্ধতি ৩: NOT IN (NULL থাকলে সমস্যা হয়, এড়িয়ে চলুন)
SELECT product_id, product_name
FROM products
WHERE product_id NOT IN (
    SELECT DISTINCT product_id
    FROM order_items
    WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
    AND product_id IS NOT NULL  -- NULL handle করতে হবে
);

ব্যাখ্যা: LEFT JOIN + IS NULL সবচেয়ে efficient। NOT IN-এ subquery-তে NULL থাকলে পুরো query কোনো result দেয় না, তাই সতর্ক থাকুন।

---

সমস্যা ৯: Pivot Table তৈরি করুন (Row থেকে Column)

সমস্যা: monthly_sales টেবিলে বছর, মাস এবং বিক্রয় আছে। Row-তে থাকা মাসগুলোকে column-এ রূপান্তর করে একটি pivot table তৈরি করুন।

টেবিল:

monthly_sales (year, month, total_sales)

প্রত্যাশিত ফলাফল:

year	Jan	Feb	Mar	Apr	...
2023	50000	45000	60000	55000	...
2024	62000	58000	71000	68000	...

সমাধান:

-- MySQL-এ CASE WHEN দিয়ে Pivot
SELECT
    year,
    SUM(CASE WHEN month = 1  THEN total_sales ELSE 0 END) AS জানুয়ারি,
    SUM(CASE WHEN month = 2  THEN total_sales ELSE 0 END) AS ফেব্রুয়ারি,
    SUM(CASE WHEN month = 3  THEN total_sales ELSE 0 END) AS মার্চ,
    SUM(CASE WHEN month = 4  THEN total_sales ELSE 0 END) AS এপ্রিল,
    SUM(CASE WHEN month = 5  THEN total_sales ELSE 0 END) AS মে,
    SUM(CASE WHEN month = 6  THEN total_sales ELSE 0 END) AS জুন,
    SUM(CASE WHEN month = 7  THEN total_sales ELSE 0 END) AS জুলাই,
    SUM(CASE WHEN month = 8  THEN total_sales ELSE 0 END) AS আগস্ট,
    SUM(CASE WHEN month = 9  THEN total_sales ELSE 0 END) AS সেপ্টেম্বর,
    SUM(CASE WHEN month = 10 THEN total_sales ELSE 0 END) AS অক্টোবর,
    SUM(CASE WHEN month = 11 THEN total_sales ELSE 0 END) AS নভেম্বর,
    SUM(CASE WHEN month = 12 THEN total_sales ELSE 0 END) AS ডিসেম্বর,
    SUM(total_sales)                                       AS বার্ষিক_মোট
FROM monthly_sales
GROUP BY year
ORDER BY year;

ব্যাখ্যা: MySQL-এ native PIVOT নেই, তাই CASE WHEN + SUM দিয়ে conditional aggregation করা হয়। PostgreSQL-এ crosstab() function ব্যবহার করা যায়।

---

সমস্যা ১০: Customer Retention Rate বের করুন

সমস্যা: orders টেবিল থেকে প্রতি মাসে নতুন এবং ফিরে আসা (returning) customer-এর সংখ্যা এবং retention rate বের করুন।

টেবিল:

orders (order_id, customer_id, order_date, total_amount)

সমাধান:

-- ধাপ ১: প্রতিটি customer-এর প্রথম order মাস বের করা
WITH first_orders AS (
    SELECT
        customer_id,
        DATE_FORMAT(MIN(order_date), '%Y-%m') AS first_month
    FROM orders
    GROUP BY customer_id
),

-- ধাপ ২: প্রতি মাসে active customers চিহ্নিত করা
monthly_activity AS (
    SELECT DISTINCT
        customer_id,
        DATE_FORMAT(order_date, '%Y-%m') AS activity_month
    FROM orders
),

-- ধাপ ৩: নতুন ও পুরনো customer আলাদা করা
classified AS (
    SELECT
        ma.activity_month,
        ma.customer_id,
        CASE
            WHEN ma.activity_month = fo.first_month THEN 'নতুন'
            ELSE 'ফিরে আসা'
        END AS customer_type
    FROM monthly_activity ma
    JOIN first_orders fo ON ma.customer_id = fo.customer_id
)

-- ধাপ ৪: মাস অনুযায়ী summary
SELECT
    activity_month              AS মাস,
    COUNT(DISTINCT CASE WHEN customer_type = 'নতুন'       THEN customer_id END) AS নতুন_কাস্টমার,
    COUNT(DISTINCT CASE WHEN customer_type = 'ফিরে আসা'  THEN customer_id END) AS ফিরে_আসা_কাস্টমার,
    COUNT(DISTINCT customer_id)                                                   AS মোট_কাস্টমার,
    ROUND(
        100.0 * COUNT(DISTINCT CASE WHEN customer_type = 'ফিরে আসা' THEN customer_id END)
              / NULLIF(COUNT(DISTINCT customer_id), 0),
        2
    )                           AS retention_rate_percent
FROM classified
GROUP BY activity_month
ORDER BY activity_month;

ব্যাখ্যা: CTE (Common Table Expression) দিয়ে ধাপে ধাপে সমস্যাটি সমাধান করা হয়েছে। NULLIF(..., 0) দিয়ে division by zero এড়ানো হয়েছে। এই ধরনের multi-step CTE solution ইন্টারভিউতে analytical thinking দেখানোর সেরা উপায়।

---

উপসংহার

এই ১০টি সমস্যা বাস্তব প্রজেক্টে এবং ইন্টারভিউতে বারবার আসে। মূল কৌশলগুলো হলো:

• জটিল সমস্যাকে CTE দিয়ে ছোট ধাপে ভাগ করুন
• Window Function (RANK, ROW_NUMBER, SUM OVER) শিখুন
• JOIN vs Subquery-এর trade-off বুঝুন
• NULL handling সবসময় মাথায় রাখুন
• EXPLAIN দিয়ে query performance যাচাই করুন