Company: Stripe | Difficulty: hard
Stripe wants to monitor each connected user's short-term earnings pattern.
For every user in users and every date from January 1 through January 5, 2024, return the user's daily earnings from user_earnings and the average earnings over that date and the previous two calendar days.
users
user_earnings
A missing user-date record in user_earnings represents zero earnings and must still be included in the calculation. Multiple user_earnings records for the same user_id on the same earning_date must be combined. Treat NULL earnings values as zero.
user_id
earning_date
NULL
earnings
For the first two dates, calculate the average using the available dates in the reporting period. Round the daily earnings and three-day average to two decimal places.
Return the user_id, earning_date, daily earnings, and three-day average. Sort by user_id descending and earning_date descending.
user_name
earning_id
For user 1, January 3 has no user_earnings record, so its daily earnings are 0.00. On January 4, the three-day average uses January 2 earnings of 150.00, January 3 earnings of 0.00, and January 4 earnings of 50.00, producing 66.67. The same date-completion rule is applied independently to user 2.
1
0.00
150.00
50.00
66.67
2
DROP TABLE IF EXISTS user_earnings; DROP TABLE IF EXISTS users; CREATE TABLE users ( user_id INT, user_name VARCHAR(100) ); CREATE TABLE user_earnings ( earning_id INT, user_id INT, earning_date DATE, earnings DECIMAL(10,2) ); INSERT INTO users VALUES (1, 'Renee Castillo'), (2, 'Diego Fernandes'); INSERT INTO user_earnings VALUES (1, 1, '2024-01-01', 100.00), (2, 1, '2024-01-02', 150.00), (3, 1, '2024-01-04', 50.00), (4, 2, '2024-01-01', 80.00), (5, 2, '2024-01-03', 40.00), (6, 2, '2024-01-04', 60.00);
← All SQL Challenges · Stripe Questions · Learn SQL Free