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.
1from collections import defaultdict
2
3def
This Python code uses a defaultdict to map each activity_date to a set of unique user_ids, accumulating only those records where the date falls between 2019-06-28 and 2019-07-27. The use of sets ensures all user_ids are distinct for each day. Finally, it returns a list of tuples showing each day and the count of its active users.