Atlassian's support team wants to understand a customer's activity immediately before submitting a support ticket. For every support ticket, find the customer's most recent web login that occurred strictly before the ticket was created. Return the ticket ID, customer ID, ticket creation time, matched login time, device type, and IP address. A login occurring at exactly the same timestamp as the ticket was created does not qualify. If no earlier login exists, return NULL for the login details. If multiple logins for the same customer have the same timestamp, use the login with the greater login ID. Preserve NULL values in the matched login record. Sort the results by ticket ID descending. ### Table Schema | Table | Column | Type | |---|---|---| | support_tickets | ticket_id | integer | | support_tickets | customer_id | integer | | support_tickets | created_at | timestamp | | support_tickets | issue_type | varchar(50) | | web_logins | login_id | integer | | web_logins | customer_id | integer | | web_logins | login_time | timestamp | | web_logins | device_type | varchar(30) | | web_logins | ip_address | varchar(45) | ### Example Input: support_tickets | ticket_id | customer_id | created_at | issue_type | |---:|---:|---|---| | 1001 | 501 | 2025-03-01 10:00:00 | Login failure | | 1002 | 501 | 2025-03-01 12:00:00 | Billing issue | | 1003 | 502 | 2025-03-01 09:00:00 | Export error | | 1004 | 503 | 2025-03-01 15:00:00 | Access request | | 1005 | 504 | 2025-03-01 08:00:00 | Sync issue | ### Example Input: web_logins | login_id | customer_id | login_time | device_type | ip_address | |---:|---:|---|---|---| | 1 | 501 | 2025-03-01 08:00:00 | desktop | 192.0.2.10 | | 2 | 501 | 2025-03-01 10:00:00 | mobile | 192.0.2.11 | | 3 | 501 | 2025-03-01 11:00:00 | tablet | 192.0.2.12 | | 4 | 502 | 2025-03-01 08:59:00 | desktop | 192.0.2.20 | | 5 | 503 | 2025-03-01 16:00:00 | mobile | 192.0.2.30 | | 6 | 504 | 2025-03-01 07:00:00 | desktop | NULL | ### Example Output Explanation Ticket 1001 does not match the 10:00 login because the login occurred exactly when the ticket was created. It therefore matches the 08:00 desktop login. Ticket 1002 matches the 11:00 tablet login because it is the latest login before the ticket. Ticket 1003 matches the 08:59 login, while ticket 1004 has no earlier login and receives NULL login details. Ticket 1005 matches the 07:00 login and preserves its NULL IP address.
DROP TABLE IF EXISTS web_logins; DROP TABLE IF EXISTS support_tickets; CREATE TABLE support_tickets ( ticket_id INT, customer_id INT, created_at TIMESTAMP, issue_type VARCHAR(50) ); CREATE TABLE web_logins ( login_id INT, customer_id INT, login_time TIMESTAMP, device_type VARCHAR(30), ip_address VARCHAR(45) ); INSERT INTO support_tickets VALUES (1001, 501, '2025-03-01 10:00:00', 'Login failure'), (1002, 501, '2025-03-01 12:00:00', 'Billing issue'), (1003, 502, '2025-03-01 09:00:00', 'Export error'), (1004, 503, '2025-03-01 15:00:00', 'Access request'), (1005, 504, '2025-03-01 08:00:00', 'Sync issue'); INSERT INTO web_logins VALUES (1, 501, '2025-03-01 08:00:00', 'desktop', '192.0.2.10'), (2, 501, '2025-03-01 10:00:00', 'mobile', '192.0.2.11'), (3, 501, '2025-03-01 11:00:00', 'tablet', '192.0.2.12'), (4, 502, '2025-03-01 08:59:00', 'desktop', '192.0.2.20'), (5, 503, '2025-03-01 16:00:00', 'mobile', '192.0.2.30'), (6, 504, '2025-03-01 07:00:00', 'desktop', NULL);