Leetcode Problem 1127. User Purchase Platform

1127. User Purchase Platform

Leetcode Solutions

Aggregating User Spending by Platform and Date

  1. Group the spending data by user_id and spend_date and calculate the total amount spent on mobile and desktop for each user on each date.
  2. Use a CASE statement to determine the platform category ('mobile', 'desktop', or 'both') for each user on each date based on the amounts calculated in step 1.
  3. Create a reference table with all possible combinations of spend_date and platform categories.
  4. Perform a LEFT JOIN between the reference table and the categorized spending data on spend_date and platform.
  5. Group the joined data by spend_date and platform and calculate the total amount spent (total_amount) and the total number of users (total_users) for each combination.
  6. Return the final result set with spend_date, platform, total_amount, and total_users.

erDiagram
    Spending {
        int user_id
        date spend_date
        enum platform
        int amount
    }

Alternative Approach Using Conditional Aggregation

Ask Question

Programming Language
image/screenshot of info(optional)
Full Screen
Loading...

Suggested Answer

Answer
Full Screen
Copy Answer Code
Loading...