67 Data Modeling Interview Questions for Data Engineers

SELECT
    u.subscription_tier,
    u.country,
    d.month,
    SUM(f.revenue_usd) AS revenue
FROM fact_ad_impression f
JOIN dim_user u ON u.user_sk = f.user_sk
JOIN dim_date d ON d.date_key = f.date_key
WHERE d.full_date >= DATE '2025-01-01'
GROUP BY u.subscription_tier, u.country, d.month
ORDER BY revenue DESC

WITH paired AS (
    SELECT
        s.machine_id,
        s.process_id,
        s.event_ts AS started_at,
        MIN(e.event_ts) AS ended_at
    FROM process_events s
    JOIN process_events e
      ON e.machine_id = s.machine_id
     AND e.process_id = s.process_id
     AND e.event_type = 'stop'
     AND e.event_ts > s.event_ts
    WHERE s.event_type = 'start'
    GROUP BY s.machine_id, s.process_id, s.event_ts
)
SELECT
    m.region,
    AVG(p.ended_at - p.started_at) AS avg_duration_sec
FROM paired p
JOIN machines m ON m.machine_id = p.machine_id
GROUP BY m.region

WITH clipped AS (
    SELECT
        s.employee_id,
        s.application_id,
        d.day,
        GREATEST(s.start_ts, d.day::TIMESTAMPTZ) AS clip_start,
        LEAST(COALESCE(s.end_ts, NOW()), (d.day + INTERVAL '1 day')::TIMESTAMPTZ) AS clip_end
    FROM app_sessions s
    CROSS JOIN LATERAL generate_series(
        DATE(s.start_ts),
        DATE(COALESCE(s.end_ts, NOW())),
        INTERVAL '1 day'
    ) AS d(day)
)
SELECT
    employee_id,
    application_id,
    day,
    SUM(EXTRACT(EPOCH FROM (clip_end - clip_start)) / 60) AS minutes_used
FROM clipped
GROUP BY employee_id, application_id, day
HAVING SUM(EXTRACT(EPOCH FROM (clip_end - clip_start)) / 3600) > 10

SELECT
    a.account_key,
    a.credit_limit,
    COALESCE(posted.posted_balance, 0) AS posted_balance,
    COALESCE(holds.pending_balance, 0) AS pending_balance,
    a.credit_limit
      - COALESCE(posted.posted_balance, 0)
      - COALESCE(holds.pending_balance, 0) AS available_credit
FROM dim_accounts a
LEFT JOIN (
    SELECT account_key, SUM(posted_amount) AS posted_balance
    FROM fact_settlements
    GROUP BY account_key
) posted ON posted.account_key = a.account_key
LEFT JOIN (
    SELECT account_key, SUM(auth_amount) AS pending_balance
    FROM fact_authorizations
    WHERE auth_status = 'open'
      AND expires_at > NOW()
    GROUP BY account_key
) holds ON holds.account_key = a.account_key
WHERE a.account_key = 550142

SELECT
    d.fiscal_week,
    s.region,
    p.category,
    SUM(f.quantity) AS units,
    SUM(f.extended_price) AS gross_sales
FROM fact_sales f
JOIN dim_date d ON d.date_key = f.date_key
JOIN dim_store s ON s.store_sk = f.store_sk
JOIN dim_product p ON p.product_sk = f.product_sk
GROUP BY d.fiscal_week, s.region, p.category

SELECT
    u.signup_date,
    u.acquisition_channel,
    (d.full_date - u.signup_date) AS days_since_signup,
    COUNT(DISTINCT a.user_key) AS returning_users
FROM fact_user_daily_activity a
JOIN dim_users u ON u.user_key = a.user_key
JOIN dim_date d ON d.date_key = a.date_key
WHERE u.signup_date >= DATE '2026-01-01'
  AND d.full_date BETWEEN u.signup_date AND u.signup_date + 90
GROUP BY u.signup_date, u.acquisition_channel, (d.full_date - u.signup_date)
ORDER BY u.signup_date, days_since_signup

SELECT
    CASE
        WHEN a.credit_score_at_apply >= 740 THEN 'prime'
        WHEN a.credit_score_at_apply >= 670 THEN 'near_prime'
        ELSE 'subprime'
    END AS tier,
    COUNT(*) AS applications,
    COUNT(*) FILTER (WHERE a.status = 'approved') AS approved,
    COUNT(f.funded_loan_id) AS funded
FROM loan_applications a
LEFT JOIN loan_offers o ON o.application_id = a.application_id
LEFT JOIN funded_loans f ON f.offer_id = o.offer_id
WHERE a.applied_at >= NOW() - INTERVAL '90 days'
GROUP BY tier
ORDER BY tier

SELECT
    c.category_name,
    u.region,
    SUM(f.price_at_sale * f.quantity) AS gmv,
    SUM(f.commission_amount) AS commission
FROM fact_transactions f
JOIN dim_user u ON u.user_sk = f.user_sk
JOIN dim_product p ON p.product_sk = f.product_sk
JOIN dim_category c ON c.category_sk = p.category_sk
JOIN dim_date d ON d.date_sk = f.date_sk
WHERE d.full_date >= DATE '2026-01-01'
GROUP BY c.category_name, u.region
ORDER BY gmv DESC

SELECT
    d.fiscal_period,
    p.tier,
    COUNT(DISTINCT s.subscriber_sk) AS active_subs,
    SUM(s.mrr_usd) AS mrr_usd,
    SUM(CASE WHEN s.end_reason = 'voluntary_churn' THEN 1 ELSE 0 END) AS churned
FROM fact_subscriptions s
JOIN dim_plans p ON p.plan_sk = s.plan_sk
JOIN dim_date d ON d.date_key = CAST(TO_CHAR(s.period_start_ts, 'YYYYMMDD') AS INT)
WHERE s.period_start_ts <= d.calendar_date
  AND (s.period_end_ts IS NULL OR s.period_end_ts > d.calendar_date)
GROUP BY d.fiscal_period, p.tier

CREATE TABLE dim_users (
    user_id    BIGINT PRIMARY KEY,
    full_name  TEXT,
    phone      TEXT,
    home_city  TEXT,
    signup_at  TIMESTAMPTZ
);

CREATE TABLE dim_vehicles (
    vehicle_id     BIGINT PRIMARY KEY,
    owner_user_id  BIGINT REFERENCES dim_users(user_id),
    make           TEXT,
    model          TEXT,
    plate          TEXT,
    seat_capacity  INT
);

CREATE TABLE dim_driver_profiles (
    user_id            BIGINT PRIMARY KEY REFERENCES dim_users(user_id),
    license_number     TEXT,
    current_vehicle_id BIGINT REFERENCES dim_vehicles(vehicle_id),
    status             TEXT,
    driver_since       DATE
);

CREATE TABLE dim_rider_profiles (
    user_id            BIGINT PRIMARY KEY REFERENCES dim_users(user_id),
    default_payment_id BIGINT,
    rider_since        DATE
);

CREATE TABLE fact_trips (
    trip_id          BIGINT PRIMARY KEY,
    driver_user_id   BIGINT REFERENCES dim_users(user_id),
    rider_user_id    BIGINT REFERENCES dim_users(user_id),
    vehicle_id       BIGINT REFERENCES dim_vehicles(vehicle_id),
    requested_at     TIMESTAMPTZ,
    completed_at     TIMESTAMPTZ,
    fare_amount      NUMERIC,
    surge_multiplier NUMERIC,
    rating_of_driver SMALLINT,
    rating_of_rider  SMALLINT
);

SELECT
    u.handle,
    SUM(h.views) AS views_24h,
    SUM(h.reactions) AS reactions_24h,
    SUM(h.shares) AS shares_24h,
    SUM(h.shares)::NUMERIC / NULLIF(SUM(h.views), 0) AS share_rate
FROM post_engagement_hourly h
JOIN posts p ON p.post_id = h.post_id
JOIN users u ON u.user_id = p.creator_id
WHERE h.hour_bucket >= NOW() - INTERVAL '24 hours'
GROUP BY u.handle
ORDER BY shares_24h DESC
LIMIT 50

SELECT
    t.name,
    t.release_year,
    STRING_AGG(DISTINCT g.genre_name, ', ') AS genres,
    STRING_AGG(DISTINCT ptr.role, ', ') AS roles
FROM titles t
JOIN person_title_roles ptr ON ptr.title_id = t.title_id
JOIN persons p ON p.person_id = ptr.person_id
LEFT JOIN title_genres tg ON tg.title_id = t.title_id
LEFT JOIN genres g ON g.genre_id = tg.genre_id
WHERE p.full_name = 'Tilda Swinton'
GROUP BY t.name, t.release_year
ORDER BY t.release_year DESC

SELECT
    p.brand,
    SUM(si.price) AS gross_revenue,
    SUM(CASE WHEN si.was_kept THEN si.price ELSE 0 END) AS net_revenue,
    AVG(CASE WHEN si.was_kept THEN 1.0 ELSE 0.0 END) AS keep_rate
FROM fact_shipment_items si
JOIN fact_shipments s ON s.shipment_sk = si.shipment_sk
JOIN dim_product p ON p.product_sk = si.product_sk
JOIN dim_date d ON d.date_key = s.shipped_date_key
WHERE d.fiscal_month = '2025-03'
GROUP BY p.brand

SELECT
    p.full_name,
    t.name AS tournament,
    SUM(s.goals) AS goals,
    SUM(s.assists) AS assists
FROM fact_player_match_stats s
JOIN players p ON p.player_id = s.player_id
JOIN matches m ON m.match_id = s.match_id
JOIN tournaments t ON t.tournament_id = m.tournament_id
JOIN player_team_memberships ptm
  ON ptm.player_id = s.player_id
 AND ptm.team_id = s.team_id
 AND m.kickoff_ts >= ptm.start_date
 AND (ptm.end_date IS NULL OR m.kickoff_ts < ptm.end_date)
WHERE t.tournament_id = 7
GROUP BY p.full_name, t.name

SELECT
    r.rep_nk,
    p.therapeutic_area,
    SUM(q.actual_units) / NULLIF(SUM(q.quota_target), 0) AS attainment_ratio,
    SUM(i.transfer_of_value) AS compliance_tov
FROM fact_quota_attainment q
JOIN dim_reps r ON r.rep_sk = q.rep_sk
JOIN dim_products p ON p.product_sk = q.product_sk
LEFT JOIN fact_sales_interactions i
  ON i.rep_sk = q.rep_sk
 AND i.product_sk = q.product_sk
 AND i.interaction_ts >= q.period_start
WHERE q.period_start = DATE '2025-01-01'
GROUP BY r.rep_nk, p.therapeutic_area