This SQL Interview Question Trips Up Most Freshers | Client Qualification Problem | APPLE INTERVIEW

In this video, we solve a multi-step SQL problem that tests your ability to break down a complex filtering condition into clean, readable logic using CTEs. The problem gives us a table of user activity events across different clients. Certain event types are classified as communication events, specifically video call received, video call sent, voice call received, and voice call sent. The task is to find which client has the highest number of qualified users, where a user qualifies for a client only if at least 50 percent of their events on that client are communication events. The approach we take is step by step. First we calculate total events and communication events per user per client. Then we apply the 50 percent threshold to filter qualified users. Then we count qualified users per client and return the one with the most. This kind of problem shows up a lot in actual data analyst interviews because it tests whether you can translate a business rule into SQL logic cleanly, not just whether you know the syntax. Use the following SQL to set up the problem and follow along: DROP TABLE IF EXISTS fact_event; CREATE TABLE fact_event ( id BIGINT PRIMARY KEY, time_id TIMESTAMP, user_id VARCHAR(50), customer_id VARCHAR(50), client_id VARCHAR(50), event_type VARCHAR(50), event_id BIGINT ); INSERT INTO fact_event (id, time_id, user_id, customer_id, client_id, event_type, event_id) VALUES (1, '2024-02-01 10:00:00', 'U1', 'C1', 'CL1', 'video call received', 101), (2, '2024-02-01 10:05:00', 'U1', 'C1', 'CL1', 'video call sent', 102), (3, '2024-02-01 10:10:00', 'U1', 'C1', 'CL1', 'message sent', 103), (4, '2024-02-01 10:15:00', 'U1', 'C1', 'CL1', 'voice call sent', 104), (5, '2024-02-01 11:00:00', 'U2', 'C2', 'CL2', 'voice call received', 105), (6, '2024-02-01 11:10:00', 'U2', 'C2', 'CL2', 'voice call sent', 106), (7, '2024-02-01 11:20:00', 'U2', 'C2', 'CL2', 'message received', 107), (8, '2024-02-01 11:25:00', 'U2', 'C2', 'CL2', 'video call sent', 108), (9, '2024-02-01 12:00:00', 'U3', 'C3', 'CL1', 'video call sent', 109), (10, '2024-02-01 12:15:00', 'U3', 'C3', 'CL1', 'voice call received', 110), (11, '2024-02-01 12:30:00', 'U3', 'C3', 'CL1', 'voice call sent', 111), (12, '2024-02-01 12:45:00', 'U3', 'C3', 'CL1', 'video call received', 112), (13, '2024-02-01 13:00:00', 'U4', 'C4', 'CL3', 'message sent', 113), (14, '2024-02-01 13:10:00', 'U4', 'C4', 'CL3', 'message received', 114), (15, '2024-02-01 14:00:00', 'U5', 'C5', 'CL2', 'video call sent', 115), (16, '2024-02-01 14:05:00', 'U5', 'C5', 'CL2', 'message sent', 116), (17, '2024-02-01 15:00:00', 'U6', 'C6', 'CL1', 'video call received', 117), (18, '2024-02-01 15:05:00', 'U6', 'C6', 'CL2', 'voice call sent', 118), (19, '2024-02-01 15:10:00', 'U6', 'C6', 'CL2', 'message sent', 119), (20, '2024-02-01 15:15:00', 'U6', 'C6', 'CL1', 'voice call received', 120), (21, '2024-02-01 16:00:00', 'U7', 'C7', 'CL3', 'video call sent', 121), (22, '2024-02-01 16:01:00', 'U7', 'C7', 'CL3', 'video call sent', 122), (23, '2024-02-01 16:02:00', 'U7', 'C7', 'CL3', 'video call received', 123), (24, '2024-02-01 16:03:00', 'U7', 'C7', 'CL3', 'message received', 124), (25, '2024-02-01 17:00:00', 'U8', 'C8', 'CL4', 'voice call sent', 125), (26, '2024-02-01 18:00:00', 'U9', 'C9', 'CL1', 'video call sent', 126), (27, '2024-02-01 18:05:00', 'U9', 'C9', 'CL2', 'video call sent', 127), (28, '2024-02-01 18:10:00', 'U9', 'C9', 'CL1', 'message sent', 128), (29, '2024-02-01 18:15:00', 'U9', 'C9', 'CL2', 'message sent', 129); If you are preparing for data analyst or business analyst interviews and want to get better at writing clean, structured SQL, subscribe and hit the bell. New problems every week on the channel.