User Activity For The Past 30 Days I - Leetcode Solution

Problem Statement:

The problem asks us to find out the daily user activity for the past 30 days. We are given a table with two columns - user_id and activity_date. The user_id column is numeric and the activity_date column is a string data type that represents the date the activity was done.

We are asked to return a result set that contains the following columns:

• activity_date: represents the date for which we are calculating the activity
• active_users: represents the number of users who did an activity on that date

Solution:

To solve this problem we need to follow the following steps:

Step 1: Convert the activity_date column to a date data type and calculate the current date.

Step 2: Calculate the date 30 days before the current date.

Step 3: Group the table by activity_date and count the number of distinct user_ids for each day.

Step 4: Join the above result with a table that contains a list of all dates between the 30 days before the current date and the current date.

Step 5: Fill in the missing dates and active_users with 0.

Step 6: Order the resultant table by activity_date in ascending order.

SQL Query:

SELECT DATE_FORMAT(dates.date, '%Y-%m-%d') as activity_date,
       IFNULL(COUNT(DISTINCT user_id), 0) as active_users
FROM (SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as date
      FROM (SELECT 0 as a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) as a
      CROSS JOIN (SELECT 0 as a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) as b
      CROSS JOIN (SELECT 0 as a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) as c) as dates
LEFT JOIN user_activity ON DATE_FORMAT(user_activity.activity_date, '%Y-%m-%d') = dates.date 
                       AND user_activity.activity_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY dates.date
ORDER BY dates.date ASC;

Explanation:

Step 1: We first use the DATE_FORMAT function to convert the activity_date column to a date data type and calculate the current date using the curdate() function.

Step 2: We calculate the date 30 days before the current date using the DATE_SUB function.

Step 3: We group the result set by activity_date and count the number of distinct user_ids for each day using the COUNT and DISTINCT functions.

Step 4: We join the above result with a table that contains a list of all dates between the 30 days before the current date and the current date. We use the CROSS JOIN function to create a table that contains all the possible dates.

Step 5: We fill in the missing dates and active_users with 0 using the IFNULL function.

Step 6: We order the resultant table by activity_date in ascending order using the ORDER BY function.

Complexity Analysis:

The query uses a cross join to generate a table of dates which, for 30 days represents 1000 rows. The main query performs a left join to count the number of distinct users for each date. The time complexity for the join operation is O(N*M) where N is the number of rows in the user_activity table and M is the number of rows in the dates table. The query has a space complexity of O(N+M) where N is the size of the user_activity table and M is the size of the dates table.

1