4 kyu

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 identifier
  • user_id(integer): the unique identifier of the user who made the transaction
  • country(string): the country from which the transaction was made
  • date(date): the date of the transaction
  • amount(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:

iduser_idcountrydateamountdays_to_reach_thresholdavg_country_days_to_reach_threshold
12020-01-011
22020-03-012
32020-04-303
42020-06-294
51Spain2020-08-285240180
112020-01-317
122020-03-316
132Spain2020-05-305120180
...
SQL
Databases

Stats:

CreatedJul 20, 2023
PublishedJul 20, 2023
Warriors Trained158
Total Skips57
Total Code Submissions556
Total Times Completed58
SQL Completions58
Total Stars3
% of votes with a positive feedback rating91% of 16
Total "Very Satisfied" Votes13
Total "Somewhat Satisfied" Votes3
Total "Not Satisfied" Votes0
Total Rank Assessments5
Average Assessed Rank
4 kyu
Highest Assessed Rank
3 kyu
Lowest Assessed Rank
6 kyu
Ad
Contributors
  • bornForThis Avatar
  • dfhwze Avatar
Ad