Home » Blog » AI/ML Engineer সিরিজ » Series 03 » Episode 07

SQL Part 2: subquery, window function, index — যেখানে junior আটকায়

analytical SQL (Series 03, Episode 07)

🟡 INTERMEDIATE Series 03 — Data — AI-এর ভিত্তি Episode 07 / 09

📑 এই পর্বে যা যা আছে

১. গল্প: "প্রতিটা শহরের top salary" — Rahim আটকে গেল
২. সমস্যা: GROUP BY দিয়ে যা করা যায় না
৩. তিন Level-এ window function বোঝা
৪. Subquery: query-র ভেতরে query
৫. CTE: subquery-কে পরিষ্কার করা (WITH)
৬. Window function কী — GROUP BY থেকে পার্থক্য
৭. ROW_NUMBER, RANK, DENSE_RANK
৮. PARTITION BY: group-ভিত্তিক ranking
৯. Running total ও moving average
১০. LAG ও LEAD: আগের/পরের row
১১. Index: query কেন দ্রুত/ধীর হয়
১২. বাস্তব উদাহরণ: BD job data-তে analytical query
১৩. একজন AI Engineer-এর দৃষ্টিতে
১৪. Boss Question
১৫. Job Requirement Decoder
১৬. সাধারণ ভুল ধারণা
১৭. Interview Prep
১৮. হাতে-কলমে (Mini Exercise)
১৯. Project Connection
২০. সারসংক্ষেপ
২১. পরবর্তী পর্বে কী শিখব

🧩 ১. গল্প: "প্রতিটা শহরের top salary" — Rahim আটকে গেল

Rahim এখন SELECT, WHERE, GROUP BY, JOIN পারে। Boss নতুন প্রশ্ন দিলেন — "প্রতিটা শহরের সবচেয়ে বেশি salary-র job কোনটা, তার title সহ দেখাও।"

Rahim: "আপা, GROUP BY city করে MAX(salary) তো বের করলাম। কিন্তু Boss তো title-ও চান! আর GROUP BY করলে তো title হারিয়ে যায়, শুধু max সংখ্যাটা থাকে..."

Nila: "একদম ঠিক আটকেছ — আর এই জায়গাতেই বেশিরভাগ junior আটকায়। GROUP BY row গুলো গুটিয়ে ফেলে, তাই detail (title) হারায়। এর সমাধান দুটো: subquery আর সবচেয়ে সুন্দর অস্ত্র window function। আজ এই দুটোই শিখবে, সাথে index — কেন query দ্রুত বা ধীর হয়।"

❓ ২. সমস্যা: GROUP BY দিয়ে যা করা যায় না

GROUP BY অনেক row-কে একটা row-এ গুটিয়ে ফেলে। কিন্তু অনেক প্রশ্নে আমরা detail row ধরে রেখেও group-ভিত্তিক হিসাব চাই:

"প্রতিটা শহরের top ৩ salary-র job" — row হারালে চলবে না।
"প্রতিটা row-তে, ওই শহরের গড় salary-ও পাশে দেখাও।"
"salary অনুযায়ী প্রত্যেকের rank কত।"
"running total — ক্রমযোগফল।"

এই ধরনের প্রশ্নের জন্যই window function তৈরি — যা group-এর হিসাব করে কিন্তু কোনো row হারায় না।

🪜 ৩. তিন Level-এ window function বোঝা

Level 1 — সহজ intuition

ভাবুন একটা class-এর marksheet। GROUP BY হলো — "প্রতিটা section-এর গড় মার্ক" (একটা সংখ্যা, ছাত্র হারিয়ে গেল)। Window function হলো — "প্রতিটা ছাত্রের নামের পাশে তার section-এর গড় মার্ক ও তার rank লিখে দাও" (সব ছাত্র থাকে, শুধু পাশে extra তথ্য যোগ হয়)।

Level 2 — technical ভাবে

Window function প্রতিটা row-এর জন্য একটা "window" (সম্পর্কিত row-এর সেট) নিয়ে হিসাব করে, কিন্তু row-গুলো গুটিয়ে ফেলে না। মূল সিনট্যাক্স: FUNCTION() OVER (PARTITION BY ... ORDER BY ...)। এখানে PARTITION BY = কোন group ধরে হিসাব, ORDER BY = window-এর ভেতরে সাজানোর ক্রম।

Level 3 — engineer দৃষ্টিতে

Engineer রা window function-কে feature engineering-এর শক্তিশালী হাতিয়ার হিসেবে দেখে — "গত ৭ দিনের moving average", "আগের transaction থেকে পার্থক্য (LAG)", "customer-এর মধ্যে rank"। এসব feature ML model-এ সরাসরি কাজে লাগে।

📦 ৪. Subquery: query-র ভেতরে query

Subquery হলো একটা query-র ভেতরে বসানো আরেকটা query। "গড়ের চেয়ে বেশি salary-র job" — একটা ক্লাসিক উদাহরণ:

-- সব job-এর গড় salary-র চেয়ে বেশি পায় এমন job SELECT title, salary FROM jobs WHERE salary > (SELECT AVG(salary) FROM jobs); -- IN দিয়ে subquery: AI/ML industry-র company-র job SELECT title, salary FROM jobs WHERE company_id IN ( SELECT id FROM companies WHERE industry = 'AI/ML' );

ভেতরের query (SELECT AVG(salary)...) আগে চলে একটা মান বের করে, তারপর বাইরের query সেটা ব্যবহার করে। কিন্তু subquery জটিল হলে পড়তে কষ্ট হয় — তার সমাধান CTE।

🧾 ৫. CTE: subquery-কে পরিষ্কার করা (WITH)

CTE (Common Table Expression) — WITH দিয়ে একটা অস্থায়ী নাম দেওয়া result তৈরি করা, যা পরে সাধারণ table-এর মতো ব্যবহার করা যায়। জটিল query অনেক পড়ার-যোগ্য হয়:

-- প্রতিটা industry-র গড় salary আগে বের করি, -- তারপর যাদের গড় 80000-এর বেশি তাদের দেখাই WITH industry_avg AS ( SELECT c.industry, AVG(j.salary) AS avg_salary FROM jobs j JOIN companies c ON j.company_id = c.id GROUP BY c.industry ) SELECT industry, avg_salary FROM industry_avg WHERE avg_salary > 80000 ORDER BY avg_salary DESC;

কখন CTE, কখন subquery? ছোট, একবার ব্যবহারের subquery inline রাখা ঠিক আছে। কিন্তু query জটিল বা একই subquery বারবার লাগলে CTE ব্যবহার করুন — পড়তে ও debug করতে সহজ হয়।

🪟 ৬. Window function কী — GROUP BY থেকে পার্থক্য

GROUP BY
অনেক row → একটা row (গুটিয়ে ফেলে)।
detail হারায়।
"প্রতিটা শহরের গড়" = ৩টা row।

Window function
row সংখ্যা একই থাকে।
প্রতিটা row-এর পাশে group-এর হিসাব বসে।
৬টা job → ৬টা row, পাশে গড়।

-- প্রতিটা job-এর পাশে ওই শহরের গড় salary SELECT title, city, salary, AVG(salary) OVER (PARTITION BY city) AS city_avg FROM jobs; -- ফল: প্রতিটা row থাকে, পাশে city_avg যোগ হয় -- title city salary city_avg -- Python Developer Dhaka 45000 82500 -- ML Engineer Dhaka 95000 82500 -- Data Analyst Chatto 40000 62500

🥇 ৭. ROW_NUMBER, RANK, DENSE_RANK

তিনটাই ranking দেয়, কিন্তু tie (সমান মান) হলে আচরণ ভিন্ন:

SELECT title, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num, RANK() OVER (ORDER BY salary DESC) AS rnk, DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk FROM jobs;

salary ROW_NUMBER RANK DENSE_RANK 120000 1 1 1 95000 2 2 2 95000 3 2 2 <- tie 70000 4 4 3 <- RANK লাফ দেয়, DENSE_RANK দেয় না 45000 5 5 4 ROW_NUMBER : সবসময় ইউনিক ১,২,৩... (tie ভাঙে ইচ্ছেমতো) RANK : tie-তে একই rank, পরেরটা লাফ দেয় (3 বাদ) DENSE_RANK : tie-তে একই rank, পরেরটা লাফ দেয় না

🗂️ ৮. PARTITION BY: group-ভিত্তিক ranking

এবার Rahim-এর মূল সমস্যা — "প্রতিটা শহরের top salary-র job" — সমাধান করি:

-- প্রতিটা শহরের ভেতরে salary অনুযায়ী rank WITH ranked AS ( SELECT title, city, salary, ROW_NUMBER() OVER ( PARTITION BY city ORDER BY salary DESC ) AS rn FROM jobs ) SELECT title, city, salary FROM ranked WHERE rn = 1; -- প্রতিটা শহরের top ১ -- top ৩ চাইলে: WHERE rn <= 3

এটাই সেই "top-N per group" প্যাটার্ন — interview-এর সবচেয়ে জনপ্রিয় SQL প্রশ্নগুলোর একটা। PARTITION BY group তৈরি করে, ORDER BY সাজায়, ROW_NUMBER() rank দেয়, তারপর বাইরে থেকে rank দিয়ে filter — মনে রাখলে অনেক প্রশ্ন সমাধান হয়ে যায়।

📈 ৯. Running total ও moving average

Window-এর সাথে ORDER BY দিলে ক্রমযোগফল (running total) বা moving average করা যায়। ধরুন একটা sales(day, amount) table:

-- ক্রমযোগফল (running total) SELECT day, amount, SUM(amount) OVER ( ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total FROM sales; -- ৩ দিনের moving average SELECT day, amount, AVG(amount) OVER ( ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg_3d FROM sales;

ROWS BETWEEN ... AND CURRENT ROW দিয়ে window-এর সীমা ঠিক করা হয় — কোন row থেকে কোন row পর্যন্ত হিসাবে ধরা হবে। এটা time-series feature engineering-এর মূল ভিত্তি।

↔️ ১০. LAG ও LEAD: আগের/পরের row

LAG আগের row-এর মান আনে, LEAD পরের row-এর — পরিবর্তন (change) হিসাব করতে দারুণ:

-- আগের দিনের বিক্রি ও আজকের সাথে পার্থক্য SELECT day, amount, LAG(amount) OVER (ORDER BY day) AS prev_day, amount - LAG(amount) OVER (ORDER BY day) AS change FROM sales;

ML-এ এটা সোনার খনি: "গত মাসের সাথে এ মাসের পার্থক্য", "আগের transaction থেকে কত দিন পর" — এসব শক্তিশালী feature সরাসরি LAG/LEAD দিয়ে তৈরি হয়।

⚡ ১১. Index: query কেন দ্রুত/ধীর হয়

একটা বইয়ের শেষে index থাকে যাতে পুরো বই না পড়ে দ্রুত একটা শব্দ খুঁজে পাওয়া যায়। Database index ঠিক তাই — একটা column-এর জন্য আলাদা সাজানো কাঠামো, যাতে খোঁজা দ্রুত হয়।

-- index ছাড়া: WHERE city = 'Dhaka' মানে পুরো table স্ক্যান -- (লাখ row হলে ধীর) -- index দিলে: শুধু Dhaka-র অংশে সরাসরি লাফ CREATE INDEX idx_jobs_city ON jobs (city); -- একাধিক column-এ যৌথ index CREATE INDEX idx_jobs_city_salary ON jobs (city, salary);

Index ছাড়া (Full Table Scan): [row1][row2][row3]...[row 1,000,000] -- সব দেখতে হয় Index সহ: city index -> Dhaka শুরু কোথায় জানা -> সরাসরি লাফ (অনেক দ্রুত, বিশেষ করে বড় table-এ)

Trade-off: index পড়া দ্রুত করে, কিন্তু লেখা (INSERT/UPDATE) একটু ধীর করে ও storage নেয়। তাই যে column-এ ঘন ঘন WHERE/JOIN হয়, শুধু সেখানেই index দিন — সব column-এ না।

🇧🇩 ১২. বাস্তব উদাহরণ: BD job data-তে analytical query

-- প্রতিটা শহরের top ২ highest-paying job, -- সাথে ওই শহরের গড় salary পাশে WITH ranked AS ( SELECT title, city, salary, ROW_NUMBER() OVER ( PARTITION BY city ORDER BY salary DESC ) AS rn, AVG(salary) OVER (PARTITION BY city) AS city_avg FROM jobs ) SELECT city, title, salary, ROUND(city_avg) AS city_avg FROM ranked WHERE rn <= 2 ORDER BY city, salary DESC;

একটা salary-benchmark dashboard (যেমন "Dhaka-তে ML Engineer গড়ে কত পায়") — বাংলাদেশে এমন product-এর পেছনে ঠিক এই ধরনের window query চলে।

👷 ১৩. একজন AI Engineer-এর দৃষ্টিতে

AI Engineer রা window function দিয়ে feature table বানায়, তারপর সেটা Pandas/ML-এ নেয়। উদাহরণ — একটা churn model-এর জন্য "প্রতিটা customer-এর আগের transaction থেকে পার্থক্য" feature:

query = """ SELECT customer_id, txn_date, amount, amount - LAG(amount) OVER ( PARTITION BY customer_id ORDER BY txn_date ) AS amount_change FROM transactions """ import pandas as pd df = pd.read_sql(query, engine) # ready-made feature সরাসরি DataFrame-এ

অনেক সময় "যা Pandas-এ ১০ লাইন লাগে" তা SQL window function-এ ১ লাইনে হয় — আর database-এ হওয়ায় বড় data-তে অনেক দ্রুত। কোনটা কোথায় করবেন সেই সিদ্ধান্তই engineer-এর দক্ষতা।

💼 ১৪. Boss Question

Boss: "Basic SQL তো শিখেছ, এই window-টুইন্ডো আবার কেন? চাকরিতে সত্যিই লাগে?"

উত্তর: লাগে, আর junior-দের ঠিক এখানেই আলাদা করা যায়। "প্রতিটা group-এর top-N", "running total", "আগের মাসের সাথে তুলনা" — এসব business report ও ML feature প্রায় প্রতিদিন লাগে। যে subquery/window function পারে না, তাকে হয় ভুল উত্তর দিতে হয়, নয়তো Python-এ ধীর ও জটিল কোড লিখতে হয়। তাই intermediate SQL মানে দ্রুত, নির্ভরযোগ্য answer — সরাসরি business value।

🔎 ১৫. Job Requirement Decoder

"Strong SQL skills including window functions and query optimization."

প্রশ্ন	উত্তর
এর মানে কী?	আপনি শুধু basic query না, ranking/aggregation-window ও index-সচেতন query লিখতে পারেন।
কোম্পানি কেন চায়?	বড় data-তে analytical report ও feature তৈরিতে window function ও index অপরিহার্য।
কোন সমস্যা সমাধান করে?	ধীর query, ভুল top-N হিসাব ও অকারণে Python-এ ভারী কাজ এড়ায়।
Junior-এর কী জানা লাগে?	subquery, CTE, ROW_NUMBER/RANK, PARTITION BY, LAG/LEAD, index-এর মূল ধারণা।
এখনই কী master লাগে না?	execution plan গভীর বিশ্লেষণ, partitioning strategy, DB internals।
GitHub-এ কীভাবে দেখাবেন?	একটা analytics project-এ window query দিয়ে report/feature বানিয়ে README-তে ব্যাখ্যা দিন।
Interview-তে কী জিজ্ঞেস করতে পারে?	"প্রতিটা group-এর top ৩ বের করুন", "RANK vs DENSE_RANK", "index কীভাবে query দ্রুত করে?"

⚠️ ১৬. সাধারণ ভুল ধারণা

ভুল ১: window function-এ WHERE rn = 1 সরাসরি লেখা। → ঠিক: window function WHERE-এ কাজ করে না; আগে subquery/CTE-তে rank বের করে বাইরে থেকে filter করুন।

ভুল ২: ROW_NUMBER আর RANK একই ভাবা। → ঠিক: tie-তে ROW_NUMBER ইউনিক থাকে, RANK একই rank দেয় ও পরে লাফায়।

ভুল ৩: PARTITION BY আর GROUP BY গুলিয়ে ফেলা। → ঠিক: GROUP BY row গুটায়; PARTITION BY row রেখে group-ভিত্তিক হিসাব পাশে বসায়।

ভুল ৪: "index দিলেই সব দ্রুত"। → ঠিক: অকারণ index লেখা ধীর করে ও storage নেয়; শুধু ঘন ঘন query হওয়া column-এ দিন।

ভুল ৫: subquery বারবার লিখে query অপাঠযোগ্য করা। → ঠিক: CTE (WITH) দিয়ে ভাগ করে পরিষ্কার রাখুন।

🎤 ১৭. Interview Prep

প্রশ্ন ১: প্রতিটা department-এর top ৩ বেতনভোগী কর্মী কীভাবে বের করবেন?
উত্তর: CTE-তে ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC), তারপর বাইরে WHERE rn <= 3।

প্রশ্ন ২: RANK আর DENSE_RANK-এর পার্থক্য?
উত্তর: দুটোই tie-তে একই rank দেয়; RANK পরের rank লাফিয়ে যায়, DENSE_RANK ধারাবাহিক থাকে।

প্রশ্ন ৩: GROUP BY আর window function-এর মূল পার্থক্য?
উত্তর: GROUP BY row গুটিয়ে কম row দেয়; window function row সংখ্যা রেখে প্রতিটা row-এর পাশে হিসাব বসায়।

প্রশ্ন ৪: Index কীভাবে query দ্রুত করে, আর trade-off কী?
উত্তর: index সাজানো কাঠামো দিয়ে full table scan এড়ায়; বিনিময়ে write ধীর হয় ও storage বাড়ে।

✍️ ১৮. হাতে-কলমে (Mini Exercise)

jobs table (আগের পর্বের) নিয়ে চেষ্টা করুন:

১. প্রতিটা row-এর পাশে সেই শহরের গড় salary দেখান (window)।
২. সব job-কে salary অনুযায়ী rank দিন (RANK ও DENSE_RANK — পার্থক্য দেখুন)।
৩. প্রতিটা শহরের সবচেয়ে বেশি salary-র job-এর title বের করুন।
৪. একটা CTE দিয়ে "গড়ের চেয়ে বেশি salary পায়" এমন job বের করুন।
৫. city column-এ একটা index বানান এবং বলুন কখন এটা কাজে লাগবে।

🚀 ১৯. Project Connection

flagship "Bangladesh Tech Career Assistant" (V2, data)-এ আমরা এখন window function দিয়ে সত্যিকারের analytical প্রশ্নের উত্তর দিতে পারব — "প্রতিটা শহরে top ৫ চাওয়া skill", "গত ৩ মাসে salary-র trend"।

Project P1 (Bangladesh Job Market Data Analyzer)-এ এই analytical query গুলো হবে core analysis। পরের পর্বে আমরা এই পরিষ্কার data-কে ML-এর জন্য প্রস্তুত করব — encoding, scaling, আর ভয়ংকর data leakage এড়ানো।

📌 ২০. সারসংক্ষেপ

এই পর্বে আমরা শিখলাম:

✓ Subquery ও CTE (WITH) দিয়ে জটিল query পরিষ্কারভাবে লেখা
✓ Window function কী — GROUP BY-এর মতো হিসাব করে কিন্তু row হারায় না
✓ ROW_NUMBER, RANK, DENSE_RANK — tie-তে এদের পার্থক্য
✓ PARTITION BY দিয়ে "top-N per group" প্যাটার্ন
✓ Running total, moving average, LAG/LEAD — time-series feature
✓ Index কীভাবে query দ্রুত করে ও এর trade-off
✓ Engineer দৃষ্টিতে — window function শক্তিশালী feature engineering-এর হাতিয়ার

➡️ ২১. পরবর্তী পর্বে কী শিখব

পরবর্তী Episode (S03E08): "Data Preprocessing — encoding, scaling, আর 'data leakage'-এর ফাঁদ"।

SQL/Pandas দিয়ে পরিষ্কার data তো পেলাম, কিন্তু ML model কাঁচা data নেয় না। পরের পর্বে শিখব কীভাবে categorical variable encode করব, number scale করব, outlier সামলাব — আর সবচেয়ে বিপজ্জনক ভুল data leakage কীভাবে এড়াব, যা অনেক junior-এর model-কে "কাগজে দুর্দান্ত, বাস্তবে ফেল" করে দেয়।

⬅ পূর্ববর্তী: SQL ১ পরবর্তী: Preprocessing ➡