Sponsored
Sponsored
This approach uses SQL aggregation functions to filter activities in the last 30 days, group the activities by date, and count distinct user IDs for each date. We'll make use of the DATE_SUB function to get the starting date and apply GROUP BY and COUNT DISTINCT functions to get the active user count per day.
Time Complexity is approximately O(n) where n is the number of records in the table, because SQL has to scan all entries to filter and group them. Space Complexity is O(k) where k is the number of unique days with activities in the range, as that's the size of the result set.
1SELECT activity_date AS day, COUNT(DISTINCT user_id) AS active_users FROM Activity WHERE activity_date BETWEEN DATE_SUB('2019-07-27', INTERVAL 29 DAY) AND '2019-07-27' GROUP BY activity_date;This SQL statement selects dates and counts distinct users who were active on those dates. It filters the dates to the last 30 days ending on 2019-07-27 using the BETWEEN clause and DATE_SUB function. The GROUP BY activity_date groups the results by each day, and COUNT(DISTINCT user_id) counts the number of unique users active on each of those days.
This approach involves using a script to fetch and process data entries to count unique users per day. The script aggregates the data manually by iterating over each record, filtering by date, and maintaining a set of unique users for each activity date.
Time Complexity is O(n), where n is the number of activity records, as each record is processed separately. Space Complexity is O(d + u) where d is the number of days in the result and u is the total number of unique users aggregated in the set for all days.
1function countActiveUsers(activities) {
2
This JavaScript function processes an array of activities to build an object with dates as keys and sets of user_ids as values. The map function on the result's entries converts the sets to their sizes, giving the count of unique users per day. The main processing loop checks activity dates to ensure they fall within the required 30-day period before updating the data structure.