SQL gotchas va intervyu

13-bob 11 kod bloki 2966 so'z

Bu bob — takrorlash varag'i. Indamay noto'g'ri javob beradigan hamma narsa bitta joyda; klassik intervyu masalalari ishlab chiqilgan holda; va qisqa uslub qo'llanmasi, chunki o'qishli SQL yozish o'zi professional ko'nikma.

Gotcha lar katalogi

Real ishda qancha zarar keltirishi bo'yicha tartiblangan. Ularning har biri muvaffaqiyatli bajariladi — ularni xavfli qiladigan narsa aynan shu.

Query topshirig'iga qanday yondashish

Intervyu bosimida talabalar qotib qoladi va yozishni boshlaydi. Ularga buning o'rniga tartib bering.

1. Savolni bitta jumlada qayta ayting. "Har bir course uchun eng yuqori bahoga ega student." Aytolmasangiz, yozolmaysiz.
2. Natijani nomlang. Nechta row qaytishi kerak — har bir course ga bittami? Har bir student ga? Qaysi column lar?
3. Table lar va kalitlarni toping. O'sha column lar qaysi table larda va ularni nima bog'laydi?
4. Uni SQL tartibida ovoz chiqarib ayting: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Ingliz jumlasi odatda to'g'ridan-to'g'ri shunga tushadi.
5. Qatlamlab quring. FROM + JOIN ni yozing, ishga tushiring. WHERE ni qo'shing, ishga tushiring. Keyin guruhlang. Hech narsani ishga tushirmasdan 30 qator yozmang.
6. Har qadamda row sonini tekshiring. JOIN dan keyin sakrasa — bu fan-out.
7. NULL va tenglik haqida so'rang. "Baholanmagan student lar sanalsinmi?" "Ikkitasi birinchi o'rin uchun teng chiqsa, ikkalasi ham keraklimi?" Intervyu oluvchilar so'raganingiz uchun ball qo'yadi — bu sizni ilgari bir marta chaqilgan ekaningizni ko'rsatadi.

Klassik intervyu masalalari

Bular qayta-qayta uchraydi. Har biri school.db ustida ishlangan, ya'ni javoblarni tekshirish mumkin.

1. Ikkinchi eng yuqori qiymat

Dunyodagi eng ko'p so'raladigan SQL savoli. Uchta yechim, har biri boshqa narsani o'rgatadi:

-- (a) DENSE_RANK — darrov N-chiga umumlashadi, tenglikni to'g'ri hal qiladi
WITH ranked AS (
    SELECT grade, DENSE_RANK() OVER (ORDER BY grade DESC) AS rnk
    FROM enrollments WHERE grade IS NOT NULL
)
SELECT DISTINCT grade FROM ranked WHERE rnk = 2;        -- 95

-- (b) LIMIT/OFFSET — sodda, lekin tenglik bo'lsa noto'g'ri
SELECT DISTINCT grade FROM enrollments
WHERE grade IS NOT NULL ORDER BY grade DESC LIMIT 1 OFFSET 1;

-- (c) "maksimumdan pastdagi maksimum" — window function lardan oldingi klassik javob
SELECT MAX(grade) FROM enrollments
WHERE grade < (SELECT MAX(grade) FROM enrollments);

1-natija

1 qator

2-natija

1 qator

3-natija

1 qator

2 ms

Nega (a) eng yaxshi javob ekanini ayting: rnk = 2 ni rnk = 5 ga o'zgartirsangiz ham ishlayveradi, va tenglik mantiqiy ishlaydi.

2. Guruh bo'yicha Top-N

WITH ranked AS (
    SELECT course_id, student_id, grade,
           ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY grade DESC) AS rn
    FROM enrollments WHERE grade IS NOT NULL
)
SELECT * FROM ranked WHERE rn <= 2 ORDER BY course_id, rn;

… 28 qatordan birinchi 15 tasi

28 qator

1 ms

1. Dublikatlarni topish

SELECT student_id, course_id, COUNT(*) AS times
FROM enrollments
GROUP BY student_id, course_id
HAVING COUNT(*) > 1;
-- (bizning toza data da yo'q)

-- va ularni o'chirish, har guruhdan eng kichik id ni qoldirib
DELETE FROM enrollments
WHERE enrollment_id NOT IN (
    SELECT MIN(enrollment_id) FROM enrollments GROUP BY student_id, course_id
);

… 514 qatordan birinchi 15 tasi

514 qator

2 ms

1. A da bor, B da mos kelmaydigan row lar

SELECT c.title FROM courses c
WHERE NOT EXISTS (SELECT 1 FROM enrollments e WHERE e.course_id = c.course_id);
-- Deep Learning

1 qator

0 ms

1. Running total

SELECT enrolled_at,
       COUNT(*) AS n,
       SUM(COUNT(*)) OVER (ORDER BY enrolled_at) AS running_total
FROM enrollments
GROUP BY enrolled_at ORDER BY enrolled_at;

… 91 qatordan birinchi 15 tasi

91 qator

1 ms

1. Median — ko'ringanidan qiyinroq Standart SQL da MEDIAN() yo'q, shuning uchun bu sizning fikrlay olishingizni sinaydi:

WITH ordered AS (
    SELECT grade,
           ROW_NUMBER() OVER (ORDER BY grade) AS rn,
           COUNT(*)     OVER ()               AS n
    FROM enrollments WHERE grade IS NOT NULL
)
SELECT AVG(grade) AS median
FROM ordered
WHERE rn IN ((n + 1) / 2, (n + 2) / 2);
-- 14 ta baho → 7 va 8-row lar → (81 + 83) / 2 = 82.0

1 qator

1 ms

(n+1)/2, (n+2)/2 usuli ikkala holatni birdan hal qiladi: toq sonda ikkala ifoda bir xil row ni, juft sonda esa o'rtadagi ikkitasini beradi.

7. Umumiy natijadagi foiz

SELECT course_id,
       COUNT(*) AS n,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM enrollments GROUP BY course_id ORDER BY pct DESC;

14 qator

1 ms

1. Ketma-ketlik / oraliq tahlili

-- Bir kunda ikkita course ga yozilgan student lar
SELECT student_id, enrolled_at, COUNT(*) AS same_day
FROM enrollments
GROUP BY student_id, enrolled_at
HAVING COUNT(*) > 1;
-- 1, 3, 5, 6, 7, 8-student lar — har biri bir kunda ikki marta yozilgan

-- Student ning ketma-ket enrollment lari orasidagi kunlar
SELECT student_id, enrolled_at,
       CAST(JULIANDAY(enrolled_at) - JULIANDAY(
           LAG(enrolled_at) OVER (PARTITION BY student_id ORDER BY enrolled_at)
       ) AS INTEGER) AS gap_days
FROM enrollments ORDER BY student_id, enrolled_at;

1-natija

… 184 qatordan birinchi 15 tasi

184 qator

2-natija

… 1866 qatordan birinchi 15 tasi

1866 qator

4 ms

1. PIVOT keyword isiz pivot

SELECT course_id,
       SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
       SUM(CASE WHEN status = 'active'    THEN 1 ELSE 0 END) AS active,
       SUM(CASE WHEN status = 'failed'    THEN 1 ELSE 0 END) AS failed
FROM enrollments GROUP BY course_id;

14 qator

1 ms

1. Har bir guruhning o'rtachadan yuqori a'zolari

SELECT enrollment_id, course_id, grade
FROM (
    SELECT *, AVG(grade) OVER (PARTITION BY course_id) AS course_avg
    FROM enrollments WHERE grade IS NOT NULL
)
WHERE grade > course_avg
ORDER BY course_id;

… 687 qatordan birinchi 15 tasi

687 qator

2 ms

O'qishli SQL yozish Intervyu oluvchilar uslubni baholaydi, hamkasblar esa uni saqlab turishga majbur. Oltita qoida yetarli:

1. Keyword lar katta harfda, o'z nomlaringiz kichik harfda. Tuzilma bir qarashda ko'rinadi.
2. Har bir clause alohida qatorda, uchtadan ko'p bo'lganda har bir column ham alohida qatorda.
3. Ma'noli table alias lari — s, e, c — hech qachon t1, t2 emas.
4. Hisoblangan column larga doim AS. Nomsiz ifodani na natijada, na pandas da o'qib bo'ladi.
5. Query ~15 qatordan oshsa, ichma-ich subquery emas, CTE. Har bir qadamga nom bering.
6. Izohda nima emas, nega ni yozing. -- test akkauntlarni chiqarib tashlash (ticket #412) foydali; -- ismni tanlash shovqin.

-- ❌ o'qish qiyin
select s.first_name,c.title,e.grade from enrollments e join students s on e.student_id=s.student_id join courses c on e.course_id=c.course_id where e.grade>80 order by e.grade desc;

-- ✅ o'sha query
SELECT
    s.first_name,
    c.title,
    e.grade
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
JOIN courses  c ON e.course_id  = c.course_id
WHERE e.grade > 80
ORDER BY e.grade DESC;

1-natija

… 338 qatordan birinchi 15 tasi

338 qator

2-natija

… 338 qatordan birinchi 15 tasi

338 qator

1 ms

To'liq savollar bankasi Mavzular bo'yicha guruhlangan — takrorlash yoki imtihon uchun. Javoblar ko'rsatilgan boblarda.

Asoslar (1-bob)

• SQL, database va DBMS orasidagi farq nima?
• Primary key, unique key va foreign key.
• NULL 0 yoki bo'sh string bilan bir xilmi? Uni qanday tekshirasiz?
• SELECT aslida qanday tartibda bajariladi?
• DELETE, TRUNCATE va DROP.
• Nega production kodda SELECT * dan qochish kerak?

Query yozish (2–4-boblar)

• Nega WHERE da column alias ini ishlatib bo'lmaydi?
• WHERE va HAVING.
• COUNT(*), COUNT(col) va COUNT(DISTINCT col).
• AVG NULL larni hisobga oladimi? Nol row ustida SUM nima qaytaradi?
• Nega SELECT 7/2 3 qaytaradi?
• DISTINCT va GROUP BY.
• WHERE city != 'X' city si NULL bo'lgan row larni qaytaradimi?

Join va subquery (5–6-boblar)

• INNER, LEFT va FULL OUTER JOIN.
• Nega LEFT JOIN dan keyin COUNT(*) 0 emas, 1 beradi?
• Shart ON da va WHERE da — nima o'zgaradi?
• A da bor, B da mos kelmaydigan row larni qanday topasiz?
• Self join nima va nega a.id < b.id?
• JOIN qo'shgandan keyin SUM ikki barobar oshdi — nega?
• IN, EXISTS va JOIN.
• CTE va subquery — qachon CTE ishlatish shart?
• Recursive CTE ning ikkita qismi qaysilar?

Window function lar (7-bob)

• Window function va aggregate.
• ROW_NUMBER, RANK va DENSE_RANK.
• Har bir guruhdan top 3 ni qanday olasiz?
• Nega window function ni WHERE da filtrlab bo'lmaydi?
• OVER() ichidagi ORDER BY nimani o'zgartiradi?
• Oydan-oyga o'sishni qanday hisoblaysiz?
• ROWS va RANGE.

Function lar va to'plamlar (8–9-boblar)

• CASE shoxni qanday tanlaydi? Pivot ni qanday quriladi?
• Nega AVG(CASE ...) ichida ELSE 0 noto'g'ri?
• Oyning oxirgi kunini qanday olasiz?
• UNION, UNION ALL va JOIN.
• UNION uchun qanday talablar bor?
• Nega EXCEPT NOT IN dan xavfsizroq?

Dizayn, performance va Python (10–12-boblar)

• 1NF, 2NF, 3NF ni tushuntiring. Qachon denormalize qilasiz?
• Transaction nima? ACID nimani anglatadi?
• View nima? U data saqlaydimi?
• Index query ni qanday tezlashtiradi va u nima turadi?
• Composite index da column tartibi muhimmi?
• "Sargable" nimani anglatadi?
• Sekin query ni qanday tashxis qilasiz?
• SQL injection nima va undan qanday himoyalanasiz?
• Filtrlashni SQL da qilish kerakmi yoki pandas da, va nega?

Xulosa — SQL qismi

O'n uchta bob, bitta kichik database va analitik SQL bo'yicha to'liq amaliy bilim. I qismni tugatgan student nima qila oladi:

• Data o'qish: SELECT, WHERE, ORDER BY, LIMIT, DISTINCT va barcha filtrlash operatorlari, jumladan NULL tuzoqlari.
• Xulosalash: beshta aggregate, GROUP BY, HAVING va row larni filtrlash bilan guruhlarni filtrlash orasidagi farq.
• Birlashtirish: barcha join turlari, anti-join lar va — eng muhimi — fan-out ni hisobotni buzishidan oldin payqash.
• Qurish: subquery, EXISTS, CTE va recursive CTE bilan o'qishli qoladigan ko'p qadamli tahlillar.
• Tahlil: window function lar bilan ranking, guruh bo'yicha top-N, running total, moving average va davrlararo o'zgarish.
• Qayta shakllantirish: CASE WHEN pivot lari, string va sana bilan ishlash, to'plam amallari va long format ga unpivot.
• Qurish va himoyalash: table lar, constraint lar, transaction lar, normalization va view lar.
• Tezlashtirish: index lar, sargable shartlar va EXPLAIN QUERY PLAN.
• Python ga ulash: parametrli query lardan toza DataFrame ga, chizishga tayyor holda.

Keyingi — II qism: 14-bob Seaborn ni boshlaydi. Bundan keyin chizadigan har bir DataFrame siz endi yozishni o'rgangan query lardan chiqadi.