Deloitte SQL interview questions

Sample Data in the activity_logs Table:

log_id	user_id	action	target_table	target_id	timestamp
1	1	logged_in	NULL	NULL	2025-01-14 08:30:00
2	1	viewed	dashboard	NULL	2025-01-14 08:35:00
3	1	created	report	102	2025-01-14 09:00:00
4	1	logged_out	NULL	NULL	2025-01-14 09:15:00
5	2	logged_in	NULL	NULL	2025-01-14 09:30:00
6	2	edited	report	103	2025-01-14 09:45:00
7	2	logged_out	NULL	NULL	2025-01-14 10:00:00

Explanation:

user_id represents the user who performed the action.
action specifies what action was taken, like logging in, creating, or editing.
target_table and target_id capture which part of the system the action relates to (optional fields).
timestamp records the exact time of the activity.

SELECT 
    u.user_id, 
    COUNT(*) AS total_logins,
    COUNT(DISTINCT CASE 
                    WHEN a.action NOT IN ('logged_in', 'logged_out') THEN a.action 
                    ELSE NULL 
                  END) AS unique_actions_count,
    MAX(a.timestamp) AS most_recent_action_time,
    (SELECT a.action 
     FROM activity_logs a 
     WHERE a.user_id = u.user_id
     ORDER BY a.timestamp DESC
     LIMIT 1) AS most_recent_action
FROM 
    activity_logs a
JOIN 
    (SELECT DISTINCT user_id 
     FROM activity_logs 
     WHERE action = 'logged_in' 
     AND timestamp >= NOW() - INTERVAL 30 DAY) u ON a.user_id = u.user_id
WHERE 
    a.action = 'logged_in'
    AND a.timestamp >= NOW() - INTERVAL 30 DAY
GROUP BY 
    u.user_id
HAVING 
    total_logins > 3
ORDER BY 
    total_logins DESC;

Explanation of the query:

Subquery for distinct user_id:
- The subquery inside the JOIN (SELECT DISTINCT user_id FROM activity_logs WHERE action = 'logged_in' AND timestamp >= NOW() - INTERVAL 30 DAY) fetches the users who have logged in during the last 30 days.
Counting total logins:
- COUNT(*) AS total_logins: Counts the total number of ‘logged_in’ actions for each user in the last 30 days.
Counting unique actions:
- COUNT(DISTINCT CASE WHEN a.action NOT IN ('logged_in', 'logged_out') THEN a.action ELSE NULL END) AS unique_actions_count: Counts the number of unique actions performed by the user, excluding ‘logged_in’ and ‘logged_out’.
Most recent action time:
- MAX(a.timestamp) AS most_recent_action_time: Finds the most recent timestamp of a ‘logged_in’ action for each user.
Most recent action performed:
- The subquery (SELECT a.action FROM activity_logs a WHERE a.user_id = u.user_id ORDER BY a.timestamp DESC LIMIT 1) retrieves the most recent action performed by the user.
Filtering users who logged in more than 3 times:
- HAVING total_logins > 3: Filters the result set to include only users who have logged in more than 3 times in the last 30 days.
Ordering the result:
- ORDER BY total_logins DESC: Orders the results in descending order of the total number of logins.