أنماط SQL الشائعة في المقابلات

أتقن هذه الأنماط المسؤول عنها بشكل متكرر في SQL للتفوق في مقابلات هندسة البيانات. كل نمط يتضمن نوع المشكلة ونهج الحل والتنويعات الشائعة.

النمط 1: إيجاد المكررات

أحد أكثر أسئلة المقابلات شيوعاً.

المكررات الأساسية

-- إيجاد البريد الإلكتروني المكرر
SELECT email, COUNT(*) as count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- الحصول على كل السجلات بالبريد الإلكتروني المكرر
SELECT *
FROM users
WHERE email IN (
    SELECT email
    FROM users
    GROUP BY email
    HAVING COUNT(*) > 1
);

احتفظ بواحد، احذف الباقي

-- حذف المكررات، احتفظ بأقل id
DELETE FROM users
WHERE id NOT IN (
    SELECT MIN(id)
    FROM users
    GROUP BY email
);

-- باستخدام CTE (PostgreSQL, SQL Server)
WITH duplicates AS (
    SELECT id,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as rn
    FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM duplicates WHERE rn > 1);

النمط 2: تحليل الفجوات

إيجاد القيم المفقودة في التسلسلات.

إيجاد الأرقام المفقودة

-- إيجاد الفجوات في معرفات الطلبات
WITH all_ids AS (
    SELECT generate_series(
        (SELECT MIN(order_id) FROM orders),
        (SELECT MAX(order_id) FROM orders)
    ) as id
)
SELECT a.id as missing_id
FROM all_ids a
LEFT JOIN orders o ON a.id = o.order_id
WHERE o.order_id IS NULL;

إيجاد فجوات التاريخ

-- إيجاد التواريخ بدون طلبات
WITH date_range AS (
    SELECT generate_series(
        '2024-01-01'::date,
        '2024-12-31'::date,
        '1 day'::interval
    )::date as date
)
SELECT d.date as missing_date
FROM date_range d
LEFT JOIN orders o ON d.date = DATE(o.order_date)
WHERE o.order_id IS NULL;

النمط 3: السجلات المتتالية

إيجاد السلاسل والتسلسلات.

أيام تسجيل الدخول المتتالية

-- إيجاد المستخدمين مع 7+ أيام تسجيل دخول متتالية
WITH login_groups AS (
    SELECT
        user_id,
        login_date,
        login_date - (ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY login_date
        ))::int as grp
    FROM (SELECT DISTINCT user_id, DATE(login_time) as login_date
          FROM logins) t
)
SELECT user_id, MIN(login_date), MAX(login_date), COUNT(*) as streak
FROM login_groups
GROUP BY user_id, grp
HAVING COUNT(*) >= 7;

شرح النمط

الخطوة	الغرض
1. احصل على تواريخ مميزة لكل مستخدم	إزالة تسجيلات الدخول المتعددة في اليوم
2. عيّن ROW_NUMBER بالتاريخ	ترقيم تسلسلي
3. اطرح من التاريخ	التواريخ المتتالية تحصل على نفس المجموعة
4. جمّع بالمجموعة المحسوبة	إيجاد طول السلسلة

النمط 4: الحسابات التراكمية

المجاميع من بداية السنة

SELECT
    month,
    revenue,
    SUM(revenue) OVER (
        PARTITION BY YEAR(date)
        ORDER BY month
    ) as ytd_revenue
FROM monthly_revenue;

النسبة المئوية من الإجمالي

SELECT
    product_id,
    sales,
    ROUND(sales * 100.0 / SUM(sales) OVER (), 2) as pct_of_total,
    ROUND(sales * 100.0 / SUM(sales) OVER (
        PARTITION BY category_id
    ), 2) as pct_of_category
FROM product_sales;

النمط 5: مشاكل القيمة الأولى/الأخيرة

أول شراء لكل عميل

-- الطريقة 1: ROW_NUMBER
WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date
        ) as rn
    FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

-- الطريقة 2: استعلام فرعي مرتبط
SELECT *
FROM orders o1
WHERE order_date = (
    SELECT MIN(order_date)
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id
);

-- الطريقة 3: DISTINCT ON (PostgreSQL)
SELECT DISTINCT ON (customer_id) *
FROM orders
ORDER BY customer_id, order_date;

أحدث تغيير حالة

SELECT
    user_id,
    status,
    changed_at,
    LAG(status) OVER (PARTITION BY user_id ORDER BY changed_at) as prev_status,
    LAG(changed_at) OVER (PARTITION BY user_id ORDER BY changed_at) as prev_changed
FROM user_status_log;

النمط 6: تقسيم الجلسات

تجميع الأحداث في جلسات (شائع في التحليلات).

-- تعريف الجلسة كأحداث ضمن 30 دقيقة
WITH session_markers AS (
    SELECT
        user_id,
        event_time,
        CASE
            WHEN event_time - LAG(event_time) OVER (
                PARTITION BY user_id ORDER BY event_time
            ) > INTERVAL '30 minutes'
            THEN 1
            ELSE 0
        END as new_session
    FROM events
),
sessions AS (
    SELECT *,
        SUM(new_session) OVER (
            PARTITION BY user_id
            ORDER BY event_time
        ) as session_id
    FROM session_markers
)
SELECT
    user_id,
    session_id,
    MIN(event_time) as session_start,
    MAX(event_time) as session_end,
    COUNT(*) as events_in_session
FROM sessions
GROUP BY user_id, session_id;

النمط 7: التحويل/عكس التحويل

الصفوف إلى أعمدة (Pivot)

-- بدون كلمة PIVOT
SELECT
    user_id,
    MAX(CASE WHEN metric = 'views' THEN value END) as views,
    MAX(CASE WHEN metric = 'clicks' THEN value END) as clicks,
    MAX(CASE WHEN metric = 'conversions' THEN value END) as conversions
FROM user_metrics
GROUP BY user_id;

الأعمدة إلى صفوف (Unpivot)

-- باستخدام UNION ALL
SELECT user_id, 'views' as metric, views as value FROM user_stats
UNION ALL
SELECT user_id, 'clicks' as metric, clicks as value FROM user_stats
UNION ALL
SELECT user_id, 'conversions' as metric, conversions as value FROM user_stats;

-- باستخدام CROSS JOIN LATERAL (PostgreSQL)
SELECT u.user_id, x.metric, x.value
FROM user_stats u
CROSS JOIN LATERAL (
    VALUES ('views', views), ('clicks', clicks), ('conversions', conversions)
) as x(metric, value);

النمط 8: التسلسلات الهرمية التكرارية

قائمة المواد (BOM)

-- إيجاد الكمية الإجمالية المطلوبة لمنتج
WITH RECURSIVE bom AS (
    -- الأساس: منتج المستوى الأعلى
    SELECT
        component_id,
        parent_id,
        quantity,
        1 as level
    FROM components
    WHERE parent_id = 'PRODUCT_A'

    UNION ALL

    -- التكراري: المكونات الفرعية
    SELECT
        c.component_id,
        c.parent_id,
        c.quantity * b.quantity as quantity,
        b.level + 1
    FROM components c
    JOIN bom b ON c.parent_id = b.component_id
)
SELECT component_id, SUM(quantity) as total_needed
FROM bom
GROUP BY component_id;

مرجع سريع: أي نمط تستخدم

نوع المشكلة	النمط
إيجاد سجلات متطابقة	المكررات + GROUP BY HAVING
قيم مفقودة في تسلسل	تحليل الفجوات + generate_series
السلاسل/التسلسلات	المتتالية + حيلة ROW_NUMBER
المقاييس التراكمية	الحسابات التراكمية + النافذة
الحصول على الأول/الأخير لكل مجموعة	ROW_NUMBER أو DISTINCT ON
التجميع القائم على الوقت	تقسيم الجلسات
إعادة تشكيل البيانات	التحويل/عكس التحويل
تجاوز الشجرة/الرسم البياني	CTE تكراري

نصيحة نجاح المقابلة: عندما تتلقى مشكلة SQL، أولاً حدد أي نمط تطابقه. هذا يساعدك على هيكلة حلك بسرعة وثقة. تدرب حتى يصبح التعرف على الأنماط تلقائياً.

الآن لننتقل إلى مفاهيم نمذجة البيانات والتخزين. :::