SQL-এর প্রচলিত প্যাটার্ন — top-N, pivot, deduplicate আর gaps-and-islands

১ · প্যাটার্ন কেন — একই প্রশ্ন বারবার ফিরে আসে

পুরান ঢাকার একজন অভিজ্ঞ বাবুর্চি প্রতিদিন নতুন রান্না আবিষ্কার করেন না। কাচ্চি, রেজালা, বোরহানি — প্রতিটির একটি পরীক্ষিত পদ্ধতি তাঁর মাথায় আছে। নতুন অনুষ্ঠানে শুধু পরিমাণ আর মসলা একটু বদলান। SQL-এর কাজও অনেকটা এমন। দোকান, ব্যাংক, বিশ্ববিদ্যালয় বা হাসপাতাল — যেখানেই কাজ করুন, ঘুরেফিরে একই ধরনের প্রশ্ন আসে। প্রতিবার শূন্য থেকে ভাবলে সময় যায়, আর ভুলও হয় একই জায়গায়।

এই পর্বটি একটি রান্নার বইয়ের মতো। এখানে দশটি query pattern আছে — অর্থাৎ বারবার আসা একটি প্রশ্নের পরীক্ষিত সমাধান-কাঠামো। প্রতিটি প্যাটার্ন চার ধাপে দেখবেন: (১) ব্যবসার প্রশ্নটি কী, (২) প্রথমে মাথায় আসা সরল চেষ্টা, যা প্রায়ই ভুল, (৩) সঠিক প্যাটার্ন, আর (৪) বিকল্প পথ ও তাদের সুবিধা-অসুবিধা। আগের পর্বগুলোর তিনটি হাতিয়ার এখানে একসাথে কাজ করবে: পর্ব ২১-এর subquery, পর্ব ২৫-এর CTE আর পর্ব ২৬-এর window function.

লাইভ SQL চলে আগের পর্বগুলোর দুটি নমুনা ডেটাবেসে। db-shop একটি অনলাইন দোকান: customers (৮ জন), categories (1 Electronics, 2 Books, 3 Clothing, 4 Grocery), products (product_id, name, category_id, price, stock — ১০টি), orders (order_id, customer_id, order_date, status — ১০টি; status হলো pending, shipped, delivered বা cancelled) আর order_items (order_id, product_id, quantity, unit_price). db-uni একটি বিশ্ববিদ্যালয়: departments, instructors, students (student_id, name, dept_id, batch, cgpa), courses (course_id, title, dept_id, credits) আর enrollments (student_id, course_id, term, grade). কিছু প্যাটার্নের জন্য ছোট আলাদা টেবিল লাগবে — messy_import, logins, wallet_ledger — সেগুলো যেখানে আসবে, সেখানেই বর্ণনা পাবেন। প্রতিটি এডিটরের নিচের “নমুনা ডেটা” বাক্সে পুরো টেবিল দেখা যায়, আর প্রতিবার চালালে ডেটাবেস নতুন করে তৈরি হয়।

২ · Top-N per group — প্রতি শ্রেণির সেরা ২টি পণ্য

ব্যবসার প্রশ্ন: দোকানের মালিক জানতে চান, প্রতিটি শ্রেণিতে (category) কোন ২টি পণ্য সবচেয়ে বেশি টাকা এনেছে — বাতিল অর্ডার বাদ দিয়ে। এটিই বিখ্যাত top-N per group সমস্যা। ক্রিকেটের ভাষায়: “প্রতিটি দলের সর্বোচ্চ রান করা ২ জন ব্যাটসম্যান”। পুরো টুর্নামেন্টের সেরা ২ জন নয়, প্রতিটি দলের আলাদা সেরা ২ জন।

সরল চেষ্টা: প্রথমে প্রতিটি পণ্যের আয় হিসাব করে ORDER BY revenue DESC LIMIT 2. এটি চলে, কিন্তু উত্তর দেয় পুরো দোকানের সেরা ২টি: Mobile Phone (29500) আর Smart Watch (7000) — দুটিই Electronics. Books, Clothing, Grocery উধাও। LIMIT পুরো ফলের ওপর একবার কাজ করে, দলের ভেতরে নয়। আরেকটি চেষ্টা হলো WHERE-এ সরাসরি window function লেখা:

SELECT name FROM products
WHERE ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) <= 2;
-- SQLite: misuse of window function ROW_NUMBER()

ত্রুটির কারণ পর্ব ১৫-এর logical processing order: WHERE চলে আগে, window function হিসাব হয় SELECT-এর সময়, অনেক পরে। WHERE যখন চলছে, তখন rn-এর কোনো অস্তিত্বই নেই। সমাধান তাই দুই ধাপ: ভেতরের ধাপে নম্বর বসানো, বাইরের ধাপে ছাঁকা। CTE এই দুই ধাপকে নাম দিয়ে পরিষ্কার করে।

WITH rev AS (            -- ধাপ ১: প্রতিটি পণ্যের আয়
  SELECT p.category_id, p.name, SUM(i.quantity * i.unit_price) AS revenue
  FROM products p
  JOIN order_items i ON i.product_id = p.product_id
  JOIN orders o ON o.order_id = i.order_id
  WHERE o.status <> 'cancelled'
  GROUP BY p.product_id, p.category_id, p.name
), ranked AS (           -- ধাপ ২: শ্রেণির ভেতরে ক্রমিক নম্বর
  SELECT category_id, name, revenue,
         ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY revenue DESC) AS rn
  FROM rev
)
SELECT category_id, name, revenue, rn   -- ধাপ ৩: ছাঁকা
FROM ranked WHERE rn <= 2
ORDER BY category_id, rn;

ROW_NUMBER ভেতরে ঠিক কী করছে, ধাপে ধাপে দেখুন:

সমান মান (tie) থাকলে কী হবে?

এবার প্রশ্নটি একটু বদলান: আয় নয়, বিক্রির সংখ্যা (কয়টি ইউনিট) দিয়ে সেরা ২টি। Electronics-এ Headphone ৩টি, আর Mobile Phone ও Smart Watch দুটিই ২টি করে বিক্রি হয়েছে। দ্বিতীয় স্থান কার? ROW_NUMBER এই প্রশ্নের উত্তর জানে না, কিন্তু তবু একজনকে 2 আর অন্যজনকে 3 দেবে — কাকে, তা নিশ্চিত নয়। RANK সমান মানকে সমান নম্বর দেয় আর পরের নম্বর লাফিয়ে যায় (1, 2, 2, 4). DENSE_RANK সমান নম্বর দেয় কিন্তু লাফায় না (1, 2, 2, 3). নিচে তিনটি একসাথে, একটি নামযুক্ত WINDOW দিয়ে, যাতে একই window তিনবার লিখতে না হয়:

WITH units AS (
  SELECT p.category_id, p.name, SUM(i.quantity) AS sold
  FROM products p
  JOIN order_items i ON i.product_id = p.product_id
  JOIN orders o ON o.order_id = i.order_id AND o.status <> 'cancelled'
  GROUP BY p.product_id, p.category_id, p.name
)
SELECT category_id, name, sold,
       ROW_NUMBER() OVER w AS rn,
       RANK()       OVER w AS rnk,
       DENSE_RANK() OVER w AS drnk
FROM units
WINDOW w AS (PARTITION BY category_id ORDER BY sold DESC)
ORDER BY category_id, sold DESC;

বিকল্প পথ আর তাদের দাম

-- units CTE আগের মতোই; "আমার চেয়ে বেশি বিক্রি এমন পণ্য ২টির কম"
SELECT u.category_id, u.name, u.sold
FROM units u
WHERE (SELECT COUNT(*) FROM units u2
       WHERE u2.category_id = u.category_id AND u2.sold > u.sold) < 2
ORDER BY u.category_id, u.sold DESC;

SELECT c.name AS category, t.name, t.revenue
FROM categories c
CROSS JOIN LATERAL (
  SELECT p.name, SUM(i.quantity * i.unit_price) AS revenue
  FROM products p
  JOIN order_items i ON i.product_id = p.product_id
  JOIN orders o ON o.order_id = i.order_id
  WHERE p.category_id = c.category_id AND o.status <> 'cancelled'
  GROUP BY p.product_id, p.name
  ORDER BY revenue DESC LIMIT 2
) AS t;
-- SQL Server: CROSS APPLY (SELECT TOP 2 ... ORDER BY revenue DESC) AS t

৩ · প্রতিটি দলের সর্বশেষ সারি — প্রতিটি গ্রাহকের শেষ অর্ডার

ব্যবসার প্রশ্ন: কাস্টমার সার্ভিস প্রতিটি গ্রাহকের সবচেয়ে নতুন অর্ডারটি দেখতে চায় — order_id আর তারিখসহ। এটি top-N-এর বিশেষ রূপ, N = 1; ইংরেজিতে প্রায়ই বলে greatest-per-group. পর্ব ২১-এ correlated subquery দিয়ে এটি একবার করেছিলেন, আর সেখানে একটি সতর্কতা ছিল: একই দিনে দুটি অর্ডার থাকলে দুটিই আসবে। এবার সেটি ইচ্ছা করে ঘটানো হলো। এডিটরের setup-এ Karima Begum (customer 2)-এর জন্য 1011 নম্বর অর্ডার যোগ করা আছে, তারিখ 2025-10-01 — তাঁর 1007 নম্বর অর্ডারের দিনেই।

সরল চেষ্টা: SELECT customer_id, MAX(order_date) FROM orders GROUP BY customer_id. তারিখ ঠিক আসে, কিন্তু order_id আনতে গেলেই সমস্যা — GROUP BY-এর বাইরের কলাম standard SQL-এ নিষিদ্ধ (পর্ব ১৮)। SQLite এখানে একটি বিশেষ ছাড় দেয়: শুধু MAX বা MIN থাকলে “bare column” সেই সারি থেকেই আসে যেখানে MAX পাওয়া গেছে। Karima-র ক্ষেত্রে দুটি সারিতেই MAX, আর SQLite আজ 1007 দিয়েছে। কিন্তু এটি SQLite-এর নিজস্ব আচরণ; PostgreSQL ত্রুটি দেয়, আর MySQL-এর ONLY_FULL_GROUP_BY মোডেও ত্রুটি আসে। এর ওপর ভরসা করবেন না। নিচে তিনটি বহনযোগ্য পথ:

-- উপায় ১: ROW_NUMBER, tiebreaker order_id
WITH r AS (SELECT customer_id, order_id, order_date,
             ROW_NUMBER() OVER (PARTITION BY customer_id
                                ORDER BY order_date DESC, order_id DESC) AS rn
           FROM orders)
SELECT customer_id, order_id, order_date FROM r WHERE rn = 1 ORDER BY customer_id;
-- উপায় ২: দলের MAX তারিখের সাথে JOIN
SELECT o.customer_id, o.order_id, o.order_date FROM orders o
JOIN (SELECT customer_id, MAX(order_date) AS last_date FROM orders GROUP BY customer_id) m
  ON m.customer_id = o.customer_id AND m.last_date = o.order_date
ORDER BY o.customer_id;
-- উপায় ৩: NOT EXISTS — এর চেয়ে নতুন কোনো অর্ডার নেই
SELECT o.customer_id, o.order_id, o.order_date FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM orders n WHERE n.customer_id = o.customer_id
  AND (n.order_date > o.order_date OR (n.order_date = o.order_date AND n.order_id > o.order_id)))
ORDER BY o.customer_id;

-- PostgreSQL-এর নিজস্ব সংক্ষিপ্ত রূপ: প্রতিটি customer_id-এর প্রথম সারি
SELECT DISTINCT ON (customer_id) customer_id, order_id, order_date
FROM orders
ORDER BY customer_id, order_date DESC, order_id DESC;

PostgreSQL-এর DISTINCT ON ঠিক এই প্যাটার্নের জন্য: ORDER BY অনুযায়ী প্রতিটি দলের প্রথম সারি রাখে। ORDER BY-এর শুরু DISTINCT ON-এর কলাম দিয়ে হতে হয়। এটি standard SQL নয়; অন্য DBMS-এ নিয়ে গেলে ROW_NUMBER-এ বদলাতে হবে।

৪ · Pivot আর unpivot — সারিকে কলাম, কলামকে সারি

ব্যবসার প্রশ্ন: ম্যানেজার একটি Excel-ধাঁচের রিপোর্ট চান: প্রতিটি মাস একটি সারি, আর প্রতিটি অর্ডার-অবস্থা (delivered, shipped, pending, cancelled) একটি করে কলাম। ডেটাবেসে কিন্তু ডেটা থাকে “লম্বা” আকারে: প্রতিটি অর্ডার একটি সারি, অবস্থা একটি কলামের মান। লম্বা থেকে “চওড়া” আকারে নেওয়াকে বলে pivot, আর এমন সারণিকে crosstab. উল্টো কাজটি, চওড়া থেকে লম্বা, unpivot.

সরল চেষ্টা: GROUP BY month, status. এটি ভুল নয়, কিন্তু ফল লম্বা: ৭টি সারি, যেমন 2025-09 | cancelled | 1, 2025-09 | delivered | 3… চোখে তুলনা করা কঠিন। চওড়া করার কৌশল হলো পর্ব ১৬–১৮-এর conditional aggregation: প্রতিটি কলামের জন্য একটি aggregate, যার ভেতরে CASE শুধু সেই অবস্থার সারি গোনে। SQL standard-এর FILTER একই কাজ পরিষ্কারভাবে লেখে; SQLite ৩.৩০ (২০১৯) আর PostgreSQL ৯.৪ থেকে এটি বোঝে। নিচে তুলনার জন্য cancelled কলামটি FILTER দিয়ে লেখা। একই এডিটরে unpivot-ও আছে: setup-এ sales_sheet নামে একটি টেবিল, যেন কেউ Excel থেকে এনেছেন — category আর দুটি মাসের কলাম (sep_2025, oct_2025); Clothing-এর অক্টোবরের ঘর ফাঁকা (NULL).

-- pivot: প্রতিটি অবস্থা একটি কলাম
SELECT SUBSTR(order_date, 1, 7) AS month,
       SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status = 'shipped'   THEN 1 ELSE 0 END) AS shipped,
       SUM(CASE WHEN status = 'pending'   THEN 1 ELSE 0 END) AS pending,
       COUNT(*) FILTER (WHERE status = 'cancelled')        AS cancelled,
       COUNT(*) AS total
FROM orders
GROUP BY month ORDER BY month;
-- unpivot: প্রতিটি মাসের কলাম আলাদা সারি
SELECT category, '2025-09' AS month, sep_2025 AS revenue FROM sales_sheet
UNION ALL
SELECT category, '2025-10', oct_2025 FROM sales_sheet WHERE oct_2025 IS NOT NULL
ORDER BY category, month;

অন্য DBMS-এর বিশেষ pivot

SELECT month, [delivered], [shipped], [pending], [cancelled]
FROM (SELECT LEFT(order_date, 7) AS month, status, order_id FROM orders) AS src
PIVOT (COUNT(order_id) FOR status IN ([delivered], [shipped], [pending], [cancelled])) AS p
ORDER BY month;

CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT * FROM crosstab(
  $$SELECT to_char(order_date::date, 'YYYY-MM'), status, COUNT(*)
    FROM orders GROUP BY 1, 2 ORDER BY 1, 2$$,
  $$VALUES ('cancelled'), ('delivered'), ('pending'), ('shipped')$$
) AS ct(month text, cancelled bigint, delivered bigint, pending bigint, shipped bigint);

সব পদ্ধতিতে একটি সীমা সাধারণ: ফলের কলামগুলো কোয়েরি লেখার সময়েই জানা থাকতে হবে। SQL-এ একটি কোয়েরির ফলের গঠন (কয়টি কলাম, কী নাম) কোয়েরি চালানোর আগেই ঠিক হয় — optimizer আর অ্যাপ দুজনেরই তা লাগে। আগামী মাসে নতুন একটি status ('returned') এলে কোনো কলাম নিজে থেকে তৈরি হবে না। “যত মাস, তত কলাম” ধরনের dynamic pivot করতে হয় অ্যাপের কোডে: আগে আলাদা কোয়েরিতে মানগুলো এনে SQL লেখা তৈরি করা। তখন মানগুলোকে কখনো সরাসরি SQL-এ জুড়বেন না; একটি অনুমোদিত তালিকার সাথে মিলিয়ে নিন, নইলে SQL injection-এর দরজা খোলে (পর্ব ৫১)। আর অনেক ক্ষেত্রে সবচেয়ে সহজ হলো লম্বা ফল পাঠিয়ে রিপোর্ট টুলে (Excel, BI টুল বা pandas) pivot করা।

৫ · Duplicate — খোঁজা, মোছা আর ঠেকানো

ব্যবসার প্রশ্ন: দোকানের তিনটি শাখা নিজেদের Excel থেকে গ্রাহক-তালিকা পাঠিয়েছে, আর সব একটি messy_import টেবিলে তোলা হয়েছে: import_id (ক্রমিক নম্বর), name, phone, city. একই মানুষ একাধিকবার এসেছেন, কিন্তু সবসময় হুবহু একইভাবে নয় — কোথাও নাম ছোট হাতের অক্ষরে, কোথাও নম্বরের আগে +88, কোথাও মাঝে ফাঁকা। আটটি সারি:

সরল চেষ্টা: SELECT DISTINCT name, phone, city. এটি ৮টির জায়গায় ৭টি সারি দেয় — শুধু Sabbir-এর হুবহু একই দুটি সারি এক হয়েছে। “Rahim Uddin” আর “rahim uddin” DISTINCT-এর চোখে আলাদা। আসল প্রশ্ন হলো, দুটি সারি কখন “একই মানুষ”? এখানে নিয়মটি ব্যবসা থেকে আসে: একই মোবাইল নম্বর মানে একই গ্রাহক। Deduplication-এর প্রথম ধাপ তাই এই “পরিচয়ের key” ঠিক করা আর তাকে একটি নিয়মিত রূপে আনা (data cleaning): ফাঁকা সরানো, আর শেষের ১১টি অঙ্ক রাখা, যাতে +880… আর 0… একই হয়।

-- ১. সরল খোঁজ: হুবহু একই phone
SELECT phone, COUNT(*) AS n FROM messy_import GROUP BY phone HAVING COUNT(*) > 1;
-- ২. আগে পরিষ্কার: ফাঁকা বাদ, শেষের ১১টি অঙ্ক; তারপর আবার খোঁজ
UPDATE messy_import SET phone = SUBSTR(REPLACE(phone, ' ', ''), -11);
SELECT phone, GROUP_CONCAT(import_id) AS ids FROM messy_import
GROUP BY phone HAVING COUNT(*) > 1;
-- ৩. প্রতিটি দলে সবচেয়ে পুরোনোটি রেখে বাকিগুলো মুছুন
DELETE FROM messy_import WHERE import_id IN (
  SELECT import_id FROM (
    SELECT import_id, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY import_id) AS rn
    FROM messy_import) WHERE rn > 1);
-- ৪. ঠেকান, তারপর upsert: নতুন হলে যোগ, পুরোনো হলে শহর হালনাগাদ
CREATE UNIQUE INDEX ux_import_phone ON messy_import(phone);
INSERT INTO messy_import (name, phone, city) VALUES
  ('Rahim Uddin', '01711000001', 'Gazipur'), ('Rakib Hasan', '01711000010', 'Dhaka')
ON CONFLICT(phone) DO UPDATE SET city = excluded.city WHERE city IS NOT excluded.city;
SELECT * FROM messy_import;

এই চার ধাপ যেকোনো deduplication কাজের কাঠামো। কয়েকটি সিদ্ধান্ত আপনাকেই নিতে হবে:

• কোনটি রাখবেন? এখানে ORDER BY import_id — প্রথম আসা সারিটি। সবচেয়ে নতুনটি চাইলে ORDER BY import_id DESC; সবচেয়ে সম্পূর্ণটি চাইলে ORDER BY (city IS NULL), import_id, যাতে শহর-থাকা সারি আগে আসে। Karima-র ক্ষেত্রে 3 রাখায় “Chattogram” টিকেছে, “Ctg” নয় — ভাগ্য ভালো ছিল। বাস্তবে আগে মুছে ফেলা সারির ভালো তথ্য রাখা সারিতে মিলিয়ে নেওয়ার (merge) ধাপও লাগে।
• মোছার আগে দেখুন। DELETE-এর ভেতরের SELECT আলাদা চালিয়ে দেখুন কোন import_id মুছবে, আর পুরো কাজ একটি transaction-এ চালান (পর্ব ৪৪), যাতে ভুল হলে ROLLBACK করা যায়। যে টেবিলে primary key-ও নেই (হুবহু একই দুটি সারি), সেখানে SQLite-এর rowid বা PostgreSQL-এর ctid দিয়ে সারি আলাদা করা যায়, অথবা DISTINCT দিয়ে নতুন টেবিল বানিয়ে পুরোনোটির জায়গায় বসানো যায়।
• ঠেকানো আসল সমাধান। পরিষ্কার করার পর UNIQUE index (বা UNIQUE constraint, পর্ব ১৩) বসান। অ্যাপে “আগে খুঁজে দেখি আছে কি না, তারপর INSERT” যথেষ্ট নয়: দুটি অনুরোধ একই মুহূর্তে এলে দুটিই “নেই” দেখে INSERT করতে পারে — একটি race condition. ডেটাবেসের UNIQUE নিয়ম এই দৌড়ে কখনো হারে না।

Upsert আর MERGE — একটু পুনরালোচনা

উপরের শেষ INSERT-টি পর্ব ১৪-এর upsert: conflict হলে ত্রুটি না দিয়ে UPDATE. excluded মানে “যে সারিটি ঢুকতে চেয়েছিল”। শেষের WHERE city IS NOT excluded.city একে শর্তসাপেক্ষ করেছে: শহর আসলেই বদলালে তবেই লেখা হবে, যাতে অকারণ লেখা, trigger বা “শেষ পরিবর্তনের সময়” না বদলায়। IS NOT ব্যবহার হয়েছে কারণ এটি NULL-এ নিরাপদ তুলনা (পর্ব ১৬); <> দিলে পুরোনো শহর NULL হলে নতুন শহর কখনো লেখা হতো না।

-- SQL Server আর Oracle-এও MERGE আছে, তবে শর্ত আর alias লেখার রীতি একটু আলাদা
MERGE INTO customers_clean AS t
USING messy_import AS s ON t.phone = s.phone
WHEN MATCHED AND t.city IS DISTINCT FROM s.city THEN
  UPDATE SET city = s.city
WHEN NOT MATCHED THEN
  INSERT (name, phone, city) VALUES (s.name, s.phone, s.city);
-- MySQL: INSERT ... ON DUPLICATE KEY UPDATE city = VALUES(city)  (8.0.20+ এ নতুন alias রীতি)

SQL:২০০৩ standard-এর MERGE একটি পুরো টেবিলকে আরেকটির সাথে মেলায়: মিললে UPDATE (বা DELETE), না মিললে INSERT. Oracle আর SQL Server বহু বছর ধরে এটি দেয়, PostgreSQL দেয় ১৫ সংস্করণ (২০২২) থেকে। SQLite আর MySQL-এ MERGE নেই; সেখানে upsert-ই পথ। মনে রাখবেন, upsert নির্ভর করে একটি UNIQUE index-এর ওপর — যে কলামে conflict ধরা হবে, সেখানে UNIQUE না থাকলে ON CONFLICT ত্রুটি দেয়। তাই deduplicate → UNIQUE → upsert, এই ক্রম।

৬ · Gaps and islands — টানা দিন আর হারানো নম্বর

ব্যবসার প্রশ্ন: একটি শেখার অ্যাপ প্রতিদিন লগইন করলে “streak” দেখায় — টানা কত দিন এসেছেন। logins টেবিলে প্রতিটি লগইন একটি সারি: user_id আর login_date. একই দিনে দুবার লগইন করলে দুটি সারি। প্রশ্ন: প্রতিটি ব্যবহারকারীর টানা দিনের প্রতিটি দল কোথা থেকে কোথা পর্যন্ত, আর কত দিনের? টানা মানগুলোর একটি দলকে বলে island (দ্বীপ), আর দুই দলের মাঝের ফাঁকা অংশকে gap. পুরো সমস্যাটির নাম তাই gaps and islands.

সরল চেষ্টা: LAG(login_date) দিয়ে আগের দিনের সাথে তুলনা করে “আগের দিনের পরের দিন কি না” জানা যায়। কিন্তু তাতে শুধু প্রতিটি সারির পাশে হ্যাঁ/না আসে — দলগুলোর শুরু, শেষ আর দৈর্ঘ্য পেতে আরও দুই ধাপ লাগে। আরেকটি সাধারণ ভুল হলো DISTINCT ভুলে যাওয়া: একই দিনের দুটি লগইন হিসাব গুলিয়ে দেয়, যা নিচে দেখবেন। এর চেয়ে সুন্দর একটি কৌশল আছে: তারিখ থেকে তার ক্রমিক নম্বর বিয়োগ করুন।

এবার দুটি ব্যবহারকারী নিয়ে পুরো কোয়েরি চালান। SQLite-এ তারিখ থেকে দিন বিয়োগ করা হয় DATE(d, '-3 days') modifier দিয়ে (পর্ব ১৭), তাই rn-কে লেখায় জুড়ে modifier বানানো হয়েছে। একই এডিটরে দ্বিতীয় প্রশ্ন: invoices টেবিলে চালান নম্বর 1 থেকে 12 হওয়ার কথা, কিন্তু কয়েকটি নেই — অডিটর জানতে চান কোনগুলো। এখানে LEAD প্রতিটি সারির পাশে পরের নম্বরটি এনে দেয়; পার্থক্য ১-এর বেশি হলেই ফাঁক।

-- island: তারিখ − ক্রমিক নম্বর = দলের নাম (anchor)
WITH d AS (SELECT DISTINCT user_id, login_date FROM logins),
g AS (SELECT user_id, login_date,
             DATE(login_date, '-' || ROW_NUMBER() OVER (PARTITION BY user_id
                  ORDER BY login_date) || ' days') AS anchor
      FROM d)
SELECT user_id, MIN(login_date) AS first_day, MAX(login_date) AS last_day,
       COUNT(*) AS streak
FROM g GROUP BY user_id, anchor ORDER BY user_id, first_day;
-- gap: পরের নম্বরের সাথে ফাঁক
SELECT invoice_no + 1 AS gap_from, next_no - 1 AS gap_to
FROM (SELECT invoice_no, LEAD(invoice_no) OVER (ORDER BY invoice_no) AS next_no
      FROM invoices)
WHERE next_no - invoice_no > 1;

একই কৌশল সংখ্যায়ও চলে (id − ROW_NUMBER()), আর “টানা” মানে যদি ১ দিনের বদলে অন্য কিছু হয় — যেমন ৩০ মিনিটের বেশি বিরতি না থাকলে একই “session” — তখন LAG দিয়ে প্রতিটি দলের শুরু চিহ্নিত করে সেই চিহ্নের running SUM-কে দলের নম্বর বানানো হয়। ওয়েবসাইটের ভিজিট বিশ্লেষণে এটিকে বলে sessionization; ভাবনার প্রশ্ন ২-এ আরও আছে।

৭ · Running total — ওয়ালেটের ব্যালান্স আর প্রথমবারের গ্রাহক

ব্যবসার প্রশ্ন: bKash বা Nagad-এর মতো একটি মোবাইল ওয়ালেটের খাতা (ledger) ভাবুন। প্রতিটি লেনদেন একটি সারি: wallet_ledger টেবিলে entry_id, wallet (মোবাইল নম্বর), txn_time, kind ('credit' মানে টাকা ঢুকেছে — Cash In বা Received; 'debit' মানে বেরিয়েছে — Send Money, Payment, Cash Out), amount (টাকা, পূর্ণসংখ্যা) আর note. ব্যালান্স আলাদা কলামে রাখা হয় না — প্রতিটি লেনদেনের পরের ব্যালান্স হলো সেই মুহূর্ত পর্যন্ত সব credit-এর যোগফল বিয়োগ সব debit-এর যোগফল। এটি একটি running total. প্রশ্ন দুটি: প্রতিটি লেনদেনের পর ব্যালান্স কত, আর কোথাও ব্যালান্স শূন্যের নিচে গেছে কি না — গেলে সেটি ডেটার ভুল বা সিস্টেমের ত্রুটি, তদন্ত দরকার।

Window function-এ SUM(…) OVER (PARTITION BY wallet ORDER BY txn_time) লিখলেই running total পাওয়া যায় — পর্ব ২৬-এ দেখেছেন। কিন্তু এখানে একটি সূক্ষ্ম ফাঁদ আছে। খাতায় entry 5 (Cash Out 2500) আর entry 7 (Received 1000) একই মিনিটে, 2025-10-03 20:10. ORDER BY থাকলে কিন্তু frame না বললে ডিফল্ট window frame হলো RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — আর RANGE সমান ORDER BY মানের সব সারিকে (peer) একসাথে নেয়।

-- সরল চেষ্টা: শুধু সময় দিয়ে সাজানো (ডিফল্ট RANGE frame)
SELECT entry_id, wallet, txn_time, note,
       SUM(CASE kind WHEN 'credit' THEN amount ELSE -amount END)
         OVER (PARTITION BY wallet ORDER BY txn_time) AS balance
FROM wallet_ledger ORDER BY wallet, txn_time, entry_id;
-- সঠিক: tiebreaker entry_id + ROWS frame, তারপর ঋণাত্মক খোঁজা
WITH b AS (
  SELECT entry_id, wallet, txn_time, note,
         SUM(CASE kind WHEN 'credit' THEN amount ELSE -amount END)
           OVER (PARTITION BY wallet ORDER BY txn_time, entry_id
                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance
  FROM wallet_ledger)
SELECT *, CASE WHEN balance < 0 THEN 'NEGATIVE' END AS flag
FROM b ORDER BY wallet, txn_time, entry_id;

প্রথমবারের গ্রাহক আর ক্রমযোজিত আলাদা গণনা

ব্যবসার প্রশ্ন: প্রতি মাসে কতজন সক্রিয় গ্রাহক, তাঁদের কতজন প্রথমবার কিনলেন, আর শুরু থেকে এ পর্যন্ত মোট কতজন আলাদা গ্রাহক পেয়েছি (বাতিল অর্ডার বাদ)? শেষ সংখ্যাটি একটি “cumulative distinct count”. সরল চেষ্টা: COUNT(DISTINCT customer_id) OVER (ORDER BY month) — SQLite ত্রুটি দেয় DISTINCT is not supported for window functions; PostgreSQL আর SQL Server-ও window-এর ভেতরে DISTINCT সমর্থন করে না। কৌশলটি হলো প্রশ্নটি উল্টে দেখা: “এ পর্যন্ত আলাদা গ্রাহক” = “এ পর্যন্ত প্রতি মাসের নতুন গ্রাহকের যোগফল”, কারণ প্রত্যেক গ্রাহক ঠিক একটি মাসেই “নতুন”। আর নতুন মানে, ওই মাসটিই তাঁর প্রথম অর্ডারের মাস (MIN).

WITH valid AS (SELECT customer_id, SUBSTR(order_date, 1, 7) AS month
               FROM orders WHERE status <> 'cancelled'),
firsts AS (SELECT customer_id, MIN(month) AS first_month
           FROM valid GROUP BY customer_id)
SELECT v.month,
       COUNT(DISTINCT v.customer_id) AS active,
       COUNT(DISTINCT f.customer_id) AS new_customers,
       SUM(COUNT(DISTINCT f.customer_id)) OVER (ORDER BY v.month) AS customers_so_far
FROM valid v
LEFT JOIN firsts f ON f.customer_id = v.customer_id AND f.first_month = v.month
GROUP BY v.month ORDER BY v.month;
-- সরল চেষ্টা (ইচ্ছা করে শেষে রাখা, কারণ এটি ত্রুটি দেয়)
SELECT SUBSTR(order_date, 1, 7) AS month,
       COUNT(DISTINCT customer_id) OVER (ORDER BY SUBSTR(order_date, 1, 7)) AS seen
FROM orders;

৮ · Median আর percentile — “সাধারণ” অর্ডারের মূল্য

ব্যবসার প্রশ্ন: একটি সাধারণ অর্ডারে গ্রাহক কত টাকা খরচ করেন? গড় (AVG) এখানে বিভ্রান্ত করে। দোকানের ১০টি অর্ডারের গড় 5682 টাকা, অথচ ১০টির ৮টিই এর চেয়ে ছোট — দুটি মোবাইল ফোনের অর্ডার (17400, 18000) গড়কে টেনে তুলেছে। একটি বড় অঙ্কের বেতন পুরো অফিসের “গড় বেতন” বাড়িয়ে দেওয়ার মতো। Median হলো সাজানো তালিকার ঠিক মাঝের মান: অর্ধেক এর নিচে, অর্ধেক ওপরে। মান জোড় সংখ্যক হলে মাঝের দুটির গড়। আরও সাধারণভাবে, p-তম percentile হলো সেই মান, যার নিচে মোটামুটি p% মান থাকে; median মানে 50th percentile.

সরল চেষ্টা: SELECT MEDIAN(total) …. SQLite-এর সাধারণ build-এ এমন কোনো ফাংশন নেই — no such function: median. (SQLite ৩.৪৭ থেকে একটি ঐচ্ছিক percentile extension আছে, যা আলাদা করে চালু করে compile করতে হয়; এই পাতার engine-এ সেটি নেই।) তাই সংজ্ঞা থেকেই বানাই: ROW_NUMBER দিয়ে ক্রমিক নম্বর, COUNT(*) OVER () দিয়ে মোট সংখ্যা n, তারপর মাঝের অবস্থান। n বিজোড় হলে মাঝের অবস্থান (n+1)/2; জোড় হলে n/2 আর n/2 + 1. পূর্ণসংখ্যার ভাগে (n + 1) / 2 আর (n + 2) / 2 দুই ক্ষেত্রেই ঠিক অবস্থানগুলো দেয়: n = 10-এ 5 আর 6, n = 7-এ 4 আর 4.

-- median: মাঝের এক বা দুটি সারির গড়
WITH t AS (SELECT order_id, SUM(quantity * unit_price) AS total
           FROM order_items GROUP BY order_id),
r AS (SELECT total, ROW_NUMBER() OVER (ORDER BY total) AS rn,
             COUNT(*) OVER () AS n FROM t)
SELECT AVG(total) AS median_total, (SELECT AVG(total) FROM t) AS mean_total
FROM r WHERE rn IN ((n + 1) / 2, (n + 2) / 2);
-- 90th percentile (nearest-rank): যেখানে CUME_DIST প্রথম 0.9 ছোঁয়
WITH t AS (SELECT order_id, SUM(quantity * unit_price) AS total
           FROM order_items GROUP BY order_id),
c AS (SELECT total, CUME_DIST() OVER (ORDER BY total) AS cd FROM t)
SELECT MIN(total) AS p90 FROM c WHERE cd >= 0.9;

WITH t AS (SELECT order_id, SUM(quantity * unit_price) AS total
           FROM order_items GROUP BY order_id)
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY total) AS median,   -- 3375
       percentile_cont(0.9) WITHIN GROUP (ORDER BY total) AS p90_cont, -- 17460
       percentile_disc(0.9) WITHIN GROUP (ORDER BY total) AS p90_disc  -- 17400
FROM t;

PostgreSQL-এ এগুলো standard-এর ordered-set aggregate: WITHIN GROUP (ORDER BY …). percentile_disc তালিকার একটি আসল মান দেয় (CUME_DIST-এর পদ্ধতির মতো), আর percentile_cont দুই মানের মাঝে রৈখিকভাবে আনুমানিক মান বসায়: অবস্থান 1 + 0.9 × (10 − 1) = 9.1, তাই 17400 + 0.1 × (18000 − 17400) = 17460. Oracle-এ সরাসরি MEDIAN() ফাংশনও আছে। MySQL-এ এগুলোর কোনোটি নেই; সেখানেও উপরের ROW_NUMBER পদ্ধতিই চলে। দলভিত্তিক median চাইলে (যেমন বিভাগভিত্তিক cgpa) দুটি window-তে PARTITION BY যোগ করে শেষে GROUP BY করুন — অনুশীলন ৪-এ সেটিই।

৯ · Relational division — “সব” কোর্স নিয়েছেন কারা

ব্যবসার প্রশ্ন: বিশ্ববিদ্যালয় জানতে চায়, কোন শিক্ষার্থীরা CSE-র সব মূল (core) কোর্সে ভর্তি হয়েছেন। এখানে মূল কোর্স মানে CSE বিভাগের ৩ ক্রেডিটের কোর্স: CSE101, CSE201, CSE301 (ল্যাব CSE302 দেড় ক্রেডিটের, তাই বাদ)। “অন্তত একটি” প্রশ্ন সহজ; “সব” প্রশ্নটিই কঠিন। একে বলে relational division, কারণ relational algebra-য় এর চিহ্ন ÷ (পর্ব ৩৩-এ বিস্তারিত)। পর্ব ২১-এর ভাবনার প্রশ্নে এর প্রথম স্বাদ পেয়েছিলেন।

সরল চেষ্টা: WHERE e.course_id IN ('CSE101', 'CSE201', 'CSE301'). ফল ৪ জন — Arif, Bristy, Dilruba, Gulshan. কিন্তু IN মানে “এর যেকোনো একটি”: Gulshan শুধু CSE101 নিয়েছেন, তবু এসেছেন। সঠিক উত্তরের জন্য দুটি পথ। প্রথমটি যুক্তিবিদ্যার সমতুল্যতা: “প্রতিটি মূল কোর্স সে নিয়েছে” ≡ “এমন কোনো মূল কোর্স নেই, যা সে নেয়নি” — প্রতীকে ∀c P(c) ≡ ¬∃c ¬P(c). দুটি “না” পাশাপাশি, তাই দুই স্তরের NOT EXISTS. দ্বিতীয়টি গোনা: যতগুলো মূল কোর্স আছে, ঠিক ততগুলো আলাদা মূল কোর্স সে নিয়েছে কি না।

-- পদ্ধতি ১: এমন কোনো মূল কোর্স নেই, যা এই শিক্ষার্থী নেননি
SELECT s.name FROM students s
WHERE NOT EXISTS (
  SELECT 1 FROM courses c
  WHERE c.dept_id = 1 AND c.credits = 3
    AND NOT EXISTS (SELECT 1 FROM enrollments e
                    WHERE e.student_id = s.student_id AND e.course_id = c.course_id));
-- পদ্ধতি ২: আলাদা মূল কোর্স গুনে মোটের সাথে মেলানো
SELECT s.name, COUNT(DISTINCT e.course_id) AS core_taken
FROM students s
JOIN enrollments e ON e.student_id = s.student_id
JOIN courses c ON c.course_id = e.course_id AND c.dept_id = 1 AND c.credits = 3
GROUP BY s.student_id, s.name
HAVING COUNT(DISTINCT e.course_id) =
       (SELECT COUNT(*) FROM courses WHERE dept_id = 1 AND credits = 3);

কোনটি বেছে নেবেন? দুই স্তরের NOT EXISTS সরাসরি সংজ্ঞাটি লেখে আর ভাজক ফাঁকা হলেও যুক্তিসম্মত আচরণ করে (ভাবনার প্রশ্ন ৫-এ এই কিনারাটি)। COUNT পদ্ধতি অনেকের কাছে পড়তে সহজ আর বড় ডেটায় প্রায়ই দ্রুত — একবার JOIN আর GROUP BY. কিন্তু এর দুটি শর্ত মানতে হয়: গোনা হবে আলাদা মান (DISTINCT), আর শুধু ভাজকের মধ্যের মান (JOIN-এ ফিল্টার)। চাকরির ইন্টারভিউতে প্রশ্নটি নানা চেহারায় আসে: “যে গ্রাহক সব শ্রেণি থেকে কিনেছেন”, “যে কর্মী সব প্রকল্পে কাজ করেছেন”।

১০ · AI-এর যুগে প্যাটার্ন

এই পর্বের প্রায় প্রতিটি প্রশ্ন AI-কে বললে সে চলার মতো একটি কোয়েরি দেবে। সমস্যা হলো, এই প্যাটার্নগুলোর ভুল কোনো ত্রুটি দেখায় না — একটি সারি কম, একটি দল বেশি, বা একটি ঋণাত্মক ব্যালান্স লুকিয়ে থাকে। প্যাটার্নের নাম জানা থাকলে আপনি AI-কে ঠিক প্রশ্নটি করতে পারবেন, আর তার উত্তরও ঠিক জায়গায় যাচাই করতে পারবেন।

AI-এর লেখা প্যাটার্ন-কোয়েরি হাতে পেলে — যাচাইয়ের চেকলিস্ট

• প্রশ্নটির প্যাটার্ন চিনেছি (top-N, latest, pivot, dedup, islands, running total, median, division) আর AI-এর কোয়েরি সেই কাঠামো মানছে
• প্রতিটি ROW_NUMBER আর running total-এর ORDER BY অনন্য — tiebreaker আছে
• tie, NULL, একই দিনে দুটি ঘটনা আর ফাঁকা দল — এই চার কিনারার ডেটায় চালিয়ে দেখেছি
• ফলের সারি-সংখ্যা হাতে মিলিয়েছি: দলপ্রতি সারি, মোট সারি, duplicate key নেই
• DELETE বা UPDATE হলে আগে একই WHERE দিয়ে SELECT চালিয়েছি, আর transaction-এর ভেতরে চালিয়েছি
• বড় টেবিলে EXPLAIN দেখে নিশ্চিত হয়েছি সাজানো বা correlated অংশ index ব্যবহার করছে