ASIN Average Price - Compute Rolling Average Price per Product. LeakCode

Question Details

Problem

You have a table of product price snapshots:

sql
CREATE TABLE price_snapshots (
    asin        VARCHAR(10),
    snapshot_dt DATE,
    price       DECIMAL(10,2)
);

For each ASIN, compute the 7-day rolling average price (average of the current day and the 6 preceding days where data exists).

Return rows with asin, snapshot_dt, and rolling_avg_price rounded to 2 decimal places.

Example

asin | snapshot_dt | price
B001 | 2024-01-01  | 10.00
B001 | 2024-01-03  | 12.00
B001 | 2024-01-07  | 14.00

-- For B001 on 2024-01-07:
-- dates in [2024-01-01, 2024-01-07] with data: 01-01, 01-03, 01-07
-- rolling_avg = (10 + 12 + 14) / 3 = 12.00

Follow-ups

How would you write this using a window function with ROWS BETWEEN vs. RANGE BETWEEN? What is the difference?
What if you need a strict 7-row window (exactly the last 7 records per ASIN), not date-based?
How would you handle ASINs that have no data for 30+ days (staleness detection)?
The table has 500M rows. What indexes and partitioning strategy would you recommend?

Full Details

Problem

You have a table of product price snapshots:

sql
CREATE TABLE price_snapshots (
    asin        VARCHAR(10),
    snapshot_dt DATE,
    price       DECIMAL(10,2)
);

For each ASIN, compute the 7-day rolling average price (average of the current day and the 6 preceding days where data exists).

Return rows with asin, snapshot_dt, and rolling_avg_price rounded to 2 decimal places.

Example

asin | snapshot_dt | price
B001 | 2024-01-01  | 10.00
B001 | 2024-01-03  | 12.00
B001 | 2024-01-07  | 14.00

-- For B001 on 2024-01-07:
-- dates in [2024-01-01, 2024-01-07] with data: 01-01, 01-03, 01-07
-- rolling_avg = (10 + 12 + 14) / 3 = 12.00

Follow-ups

How would you write this using a window function with ROWS BETWEEN vs. RANGE BETWEEN? What is the difference?
What if you need a strict 7-row window (exactly the last 7 records per ASIN), not date-based?
How would you handle ASINs that have no data for 30+ days (staleness detection)?
The table has 500M rows. What indexes and partitioning strategy would you recommend?

Free preview. Unlock all questions →

Topics

Coding Onsite Phone

More from Amazon

Reddit 175 k remote offer or 300 k in Bay Area Reddit Done with 3 rounds of On-site Amazon SDE 1 interview , but no communication after that. What to do? Reddit Nned direction for future prepration. Reddit Have an Amazon. SDE1 interview in 4 days, need tips Reddit After many years in the industry, I still struggle with textbook definitions in interviews