Days to Reach a Cumulative Transaction Threshold: part 2
Description:
You are a data analyst for a streaming service provider that operates on a tiered subscription model. Understanding user behavior to optimize this model is crucial. One specific area of interest is to monitor the spending pattern of users from the time they join the platform. This will help identify how long it typically takes for users to reach certain spending thresholds, and in turn, informs strategies for tier adjustments.
They are particularly interested in finding out how long it takes for customers from different countries to reach a certain transaction threshold. This information will help them understand market dynamics better and devise localized strategies.
You are given a transactions table with the following columns:
id
(integer): the transaction's unique identifieruser_id
(integer): the unique identifier of the user who made the transactioncountry
(string): the country from which the transaction was madedate
(date): the date of the transactionamount
(integer): the amount of the transaction
Your task is to write a SQL query that retrieves transactions for each user where the cumulative amount for that specific user from the first his/her transaction in the system to the row where, when arranged by transaction id
in ascending order, reaches or exceeds a limit of 15
While id
, date
and amount
should be displayed for every row of result set, the query should only display the user_id
and country
in the last row of each user's data (with all other rows being NULL).
In addition to columns from transactions
table, the result set should include a column days_to_reach_threshold
which indicates the number of days it took for each user to reach the cumulative transaction amount of 15. In the same logic with user_id
and country
, this value should be displayed only in the last row for each user (where all other rows are NULL).
Finally, the result set should also include a column avg_country_days_to_reach_threshold
which shows the average of days_to_reach_threshold
for all users from the same country. This value should be rounded to the nearest integer and also should be displayed only in the last row for each user (where all other rows are NULL).
The final result set should be ordered by user_id and id in ascending order. When user_id is NULL (i.e., not the last row of each user's data), these rows should be sorted to the end.
GLHF!
Notes:
- all users in the given dataset have exceeded this threshold of 15 in their total transaction amount.
- it is guaranteed that the transactions are in chronological order for each user.
- it is guaranteed that all transactions for each user are from the same country
Desired Output
The desired output should look like this:
id | user_id | country | date | amount | days_to_reach_threshold | avg_country_days_to_reach_threshold |
---|---|---|---|---|---|---|
1 | 2020-01-01 | 1 | ||||
2 | 2020-03-01 | 2 | ||||
3 | 2020-04-30 | 3 | ||||
4 | 2020-06-29 | 4 | ||||
5 | 1 | Spain | 2020-08-28 | 5 | 240 | 180 |
11 | 2020-01-31 | 7 | ||||
12 | 2020-03-31 | 6 | ||||
13 | 2 | Spain | 2020-05-30 | 5 | 120 | 180 |
Similar Kata:
Stats:
Created | Jul 20, 2023 |
Published | Jul 20, 2023 |
Warriors Trained | 158 |
Total Skips | 57 |
Total Code Submissions | 556 |
Total Times Completed | 58 |
SQL Completions | 58 |
Total Stars | 3 |
% of votes with a positive feedback rating | 91% of 16 |
Total "Very Satisfied" Votes | 13 |
Total "Somewhat Satisfied" Votes | 3 |
Total "Not Satisfied" Votes | 0 |
Total Rank Assessments | 5 |
Average Assessed Rank | 4 kyu |
Highest Assessed Rank | 3 kyu |
Lowest Assessed Rank | 6 kyu |