Task
At LinkUp, the analytics team wants to measure how efficiently friend requests are being accepted each day. Write a SQL query to calculate the daily acceptance rate, defined as:
accepted requests sent on that day / total requests sent on that day
Requirements
- Return one row per request date from the
friend_requests table.
- Count a request as accepted only when
accepted_at is not null.
- Include days with zero accepted requests.
- Return the columns:
request_date, requests_sent, requests_accepted, and acceptance_rate rounded to 2 decimal places.
- Order the result by
request_date ascending.
Table Definitions
user_logins
| column | type | description |
|---|
| login_id | INT | Primary key for each login event |
| user_id | INT | User who logged in |
| login_at | TIMESTAMP | Login timestamp |
| device_type | VARCHAR(20) | Device used for login |
friend_requests
| column | type | description |
|---|
| request_id | INT | Primary key for each friend request |
| sender_id | INT | User who sent the request |
| receiver_id | INT | User who received the request |
| requested_at | TIMESTAMP | When the request was sent |
| accepted_at | TIMESTAMP | When the request was accepted, if ever |
Sample Data
friend_requests
| request_id | sender_id | receiver_id | requested_at | accepted_at |
|---|
| 3 | 103 | 104 | 2024-03-02 08:30:00 | 2024-03-03 10:00:00 |
| 1 | 101 | 102 | 2024-03-01 09:00:00 | 2024-03-01 10:15:00 |
| 6 | 106 | 107 | 2024-03-03 09:10:00 | 2024-03-03 09:45:00 |
| 2 | 101 | 103 | 2024-03-01 11:00:00 | null |
| 8 | 108 | 109 | 2024-03-04 08:00:00 | null |
| 4 | 104 | 105 | 2024-03-02 12:00:00 | null |
Expected Output
| request_date | requests_sent | requests_accepted | acceptance_rate |
|---|
| 2024-03-01 | 2 | 1 | 0.50 |
| 2024-03-02 | 2 | 1 | 0.50 |
| 2024-03-03 | 2 | 2 | 1.00 |
| 2024-03-04 | 2 | 0 | 0.00 |
| 2024-03-05 | 2 | 1 | 0.50 |
