DW SQL Practice

Find each product's weighted average selling price. Join sales to the price effective on the purchase date. Round to two decimals; products without sales return zero.

Public problem reference · Sample data adapted for this handbook.

Price intervals for a product do not overlap. Both start_date and end_date are inclusive.

UnitsSold may contain duplicate rows; the problem treats them as sales records, so do not remove them without a business rule.

ID + effective interval → weighted revenue / units → default zero.

-- Match both product ID and the inclusive price interval.
SELECT
    p.product_id,
    COALESCE(
        ROUND(
            SUM(p.price * s.units) / NULLIF(SUM(s.units), 0),
            2
        ),
        0
    ) AS average_price
FROM Prices p
LEFT JOIN UnitsSold s
    ON p.product_id = s.product_id
   AND s.purchase_date >= p.start_date
   AND s.purchase_date <= p.end_date
GROUP BY
    p.product_id
ORDER BY
    p.product_id;

Find each product's price on 2019-08-16. Each price change stays effective until the next change. Initial price is 10.

Public problem reference · Sample data adapted for this handbook.

Changes on the target date are already effective. Ignore changes after the target date.

The pair (product_id, change_date) is unique. Include products whose only changes occur in the future.

All IDs → latest date before cutoff → look up value → default 10.

WITH all_products AS (
    SELECT DISTINCT
        product_id
    FROM Products
),
latest_date AS (
    SELECT
        product_id,
        MAX(change_date) AS change_date
    FROM Products
    WHERE change_date <= '2019-08-16'
    GROUP BY
        product_id
)
SELECT
    a.product_id,
    COALESCE(p.new_price, 10) AS price
FROM all_products a
LEFT JOIN latest_date l
    ON a.product_id = l.product_id
LEFT JOIN Products p
    ON l.product_id = p.product_id
   AND l.change_date = p.change_date
ORDER BY
    a.product_id;

For each complete seven-day window, return the total restaurant revenue and average daily revenue. Include the current date and previous six dates.

Public problem reference · Sample data adapted for this handbook.

At least one customer visits every calendar day. Multiple customers may visit on the same date.

Output begins on the seventh date. The denominator is seven calendar days, not the number of customer transactions.

Daily totals → current date + six preceding dates → total / 7.

WITH daily AS (
    -- Multiple customers on one date become one daily total.
    SELECT
        visited_on,
        SUM(amount) AS daily_amount
    FROM Customer
    GROUP BY
        visited_on
)
SELECT
    d.visited_on,
    SUM(h.daily_amount) AS amount,
    ROUND(SUM(h.daily_amount) / 7.0, 2) AS average_amount
FROM daily d
JOIN daily h
    ON h.visited_on >= DATE_SUB(d.visited_on, INTERVAL 6 DAY)
   AND h.visited_on <= d.visited_on
GROUP BY
    d.visited_on
HAVING COUNT(*) = 7
ORDER BY
    d.visited_on;

WITH daily AS (
    SELECT
        visited_on,
        SUM(amount) AS daily_amount
    FROM Customer
    GROUP BY
        visited_on
),
rolling AS (
    SELECT
        visited_on,
        SUM(daily_amount) OVER (
            ORDER BY visited_on
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) AS amount,
        COUNT(*) OVER (
            ORDER BY visited_on
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) AS days_in_window
    FROM daily
)
SELECT
    visited_on,
    amount,
    ROUND(amount / 7.0, 2) AS average_amount
FROM rolling
WHERE days_in_window = 7
ORDER BY
    visited_on;

Calculate a three-day rolling average of tweet counts for each user, rounded to two decimals.

Public problem reference · Sample data adapted for this handbook.

This practice sample has one row per user/date and consecutive dates for each user.

Include the current day and preceding two days. At the beginning, average only the available rows.

PARTITION BY user → ORDER BY date → current row + two previous rows.

SELECT
    user_id,
    tweet_date,
    ROUND(
        AVG(tweet_count) OVER (
            PARTITION BY user_id
            ORDER BY tweet_date
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ),
        2
    ) AS rolling_avg_3d
FROM tweets
ORDER BY
    user_id,
    tweet_date;

Given transaction details and a historical user-risk dimension, return each user’s daily amount, the risk effective on that date, and the average daily amount over the previous three calendar days.

Focused reconstruction based on candidate recollection, not a verified original OKX question. SQL dialect: Spark SQL.

A user’s risk validity intervals do not overlap. Each user/date matches at most one dimension row, so the temporal JOIN does not duplicate daily metrics.

For output date D, history is [D−3 days, D). Include all user transactions regardless of historical risk. Missing dates contribute zero; divide by 3. Assume complete history and non-NULL amounts in one currency.

Match effective_date <= D < expiry_date. NULL expiry means open-ended. Keep unmatched daily records as Unknown. Output only dates with transactions.

version_id identifies a dimension version. With non-overlapping validity intervals, this SQL is identical with or without the version_id column; date conditions already select the unique valid version.

The old version ends exactly where the new version begins, but the old expiry boundary is exclusive. Therefore October 18 does not match both versions. No ROW_NUMBER, ranked CTE or rn = 1 filter is needed.

WITH daily AS (
    SELECT
        user_id,
        transaction_date,
        SUM(amount) AS daily_amount
    FROM transactions
    GROUP BY
        user_id,
        transaction_date
),
metrics AS (
    SELECT
        d.user_id,
        d.transaction_date,
        d.daily_amount,
        ROUND(
            COALESCE(SUM(h.daily_amount), 0) / 3.0,
            2
        ) AS previous_3_days_avg_amount
    FROM daily d
    LEFT JOIN daily h
        ON d.user_id = h.user_id
       AND h.transaction_date >= DATE_SUB(
            d.transaction_date,
            3
       )
       AND h.transaction_date < d.transaction_date
    GROUP BY
        d.user_id,
        d.transaction_date,
        d.daily_amount
)
SELECT
    m.user_id,
    m.transaction_date,
    COALESCE(r.risk_level, 'Unknown') AS risk_level,
    m.daily_amount,
    m.previous_3_days_avg_amount
FROM metrics m
LEFT JOIN dim_user_risk r
    ON m.user_id = r.user_id
   AND m.transaction_date >= r.effective_date
   AND (
        m.transaction_date < r.expiry_date
        OR r.expiry_date IS NULL
   )
ORDER BY
    m.user_id,
    m.transaction_date;

Recall: daily totals → self join previous three dates → direct temporal LEFT JOIN. The historical self join intentionally finds multiple dates and aggregates them; the non-overlapping risk JOIN attaches at most one risk row.