ℹ️ Select 'Choose Exercise', or randomize 'Next Random Exercise' in selected language.

Choose Exercise:
Timer 00:00
WPM --
Score --
Acc --
Correct chars --

Audit Log Analysis with CTE

SQL MariaDB

Goal -- WPM

Ready
Exercise Algorithm Area
1WITH UserActionSequence AS (

2SELECT

3log_id,

4user_id,

5action_type,

6action_timestamp,

7ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY action_timestamp ASC) as rn

8FROM

9audit_log

10WHERE

11action_type IN ('login', 'perform_critical_action') -- Relevant actions

12)

13SELECT

14uas1.user_id,

15uas1.action_timestamp AS login_time,

16uas2.action_timestamp AS critical_action_time,

17TIMESTAMPDIFF(SECOND, uas1.action_timestamp, uas2.action_timestamp) AS time_diff_seconds

18FROM

19UserActionSequence uas1

20JOIN

21UserActionSequence uas2 ON uas1.user_id = uas2.user_id

22WHERE

23uas1.action_type = 'login'

24AND uas2.action_type = 'perform_critical_action'

25AND uas1.rn < uas2.rn -- Ensure login happened before critical action

26AND TIMESTAMPDIFF(SECOND, uas1.action_timestamp, uas2.action_timestamp) BETWEEN 0 AND 600 -- Within 10 minutes (600 seconds)

27ORDER BY

28uas1.user_id,

29uas1.action_timestamp;

30

31-- Helper function to check if a user performed a critical action after login within a time window

32DELIMITER $$

33CREATE FUNCTION DidUserPerformCriticalActionAfterLogin(

34p_user_id INT,

35login_time DATETIME,

36time_window_seconds INT

37) RETURNS BOOLEAN

38READS SQL DATA

39BEGIN

40DECLARE critical_action_count INT;

41SELECT COUNT(*)

42INTO critical_action_count

43FROM audit_log

44WHERE

45user_id = p_user_id

46AND action_type = 'perform_critical_action'

47AND action_timestamp > login_time

48AND TIMESTAMPDIFF(SECOND, login_time, action_timestamp) <= time_window_seconds;

49

50RETURN critical_action_count > 0;

51END$$

52DELIMITER ;

53

54-- Edge case: No 'login' or 'perform_critical_action' events.

55-- The CTE will be empty or contain only one type of action, leading to no results in the final SELECT.

56

57-- Edge case: User logs in multiple times before performing the critical action.

58-- The ROW_NUMBER() in the CTE assigns a unique rank to each relevant action for a user.

59-- The condition `uas1.rn < uas2.rn` ensures that we only consider logins that occurred chronologically before the critical action.

60-- If multiple logins exist, the query will check each login against the critical action.

61

62-- Edge case: Critical action occurs before login.

63-- The condition `uas1.rn < uas2.rn` and `TIMESTAMPDIFF(SECOND, uas1.action_timestamp, uas2.action_timestamp) BETWEEN 0 AND 600` correctly filters these out.

64

65-- Edge case: Time window is zero or negative.

66-- The BETWEEN clause handles this; if time_window_seconds is 0, only actions at the exact same timestamp (unlikely) would match.

67

68-- The CTE `UserActionSequence` pre-filters and ranks relevant log entries per user, simplifying the self-join.

69-- This approach is efficient for analyzing sequential user behavior.
Algorithm description viewbox

Audit Log Analysis with CTE

Algorithm description:

This SQL query analyzes audit logs to identify users who performed a critical action within a specific time window (e.g., 10 minutes) after logging in. It uses a Common Table Expression (CTE) to first identify and rank relevant login and critical action events for each user, then performs a self-join on the CTE to find pairs of login and critical action events that meet the temporal proximity criteria. This is vital for security analysis, detecting suspicious activity, and understanding user workflows.

Algorithm explanation:

The query uses a CTE `UserActionSequence` to filter for 'login' and 'perform_critical_action' events and assigns a sequential rank (`rn`) to each event per user based on `action_timestamp`. The main query then self-joins this CTE (`uas1` for login, `uas2` for critical action). The join conditions ensure `uas1.user_id = uas2.user_id`, `uas1.action_type = 'login'`, `uas2.action_type = 'perform_critical_action'`, `uas1.rn < uas2.rn` (login precedes critical action), and the `TIMESTAMPDIFF` condition checks if the critical action occurred within 600 seconds (10 minutes) after the login. Time complexity is O(N log N) due to ranking and sorting, where N is the number of log entries. Space complexity is O(N) for the CTE. Edge cases include missing actions, multiple logins/actions, and incorrect temporal ordering.

Pseudocode:

WITH UserActionSequence AS (
  SELECT
    log_id, user_id, action_type, action_timestamp,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY action_timestamp ASC) as rn
  FROM audit_log
  WHERE action_type IN ('login', 'critical_action')
)
SELECT
  uas1.user_id, uas1.action_timestamp AS login_time, uas2.action_timestamp AS critical_action_time,
  TIMESTAMPDIFF(SECOND, uas1.action_timestamp, uas2.action_timestamp) AS time_diff_seconds
FROM UserActionSequence uas1
JOIN UserActionSequence uas2 ON uas1.user_id = uas2.user_id
WHERE
  uas1.action_type = 'login'
  AND uas2.action_type = 'critical_action'
  AND uas1.rn < uas2.rn
  AND TIMESTAMPDIFF(SECOND, uas1.action_timestamp, uas2.action_timestamp) BETWEEN 0 AND 600
ORDER BY uas1.user_id, uas1.action_timestamp;