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
friend_requests table.accepted_at is not null.request_date, requests_sent, requests_accepted, and acceptance_rate rounded to 2 decimal places.request_date ascending.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 |
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 |
| 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 |
| Column | Type | Description |
|---|---|---|
| login_idPK | INT | Primary key for each login event |
| user_id | INT | User who logged in |
| login_at | TIMESTAMP | Timestamp of the login event |
| device_type | VARCHAR(20) | Device used for login |
| Column | Type | Description |
|---|---|---|
| request_idPK | 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 | Timestamp when the request was sent |
| accepted_at | TIMESTAMP | Timestamp when the request was accepted; null if not accepted |
| login_id | user_id | login_at | device_type |
|---|---|---|---|
| 1 | 101 | 2024-03-01 08:00:00 | ios |
| 2 | 102 | 2024-03-01 08:10:00 | web |
| 3 | 103 | 2024-03-02 07:55:00 | android |
| 4 | 104 | 2024-03-02 09:20:00 | ios |
| 5 | 105 | 2024-03-03 10:00:00 | web |
| 6 | 106 | 2024-03-03 10:05:00 | |
| 7 | 107 | 2024-03-04 11:30:00 | android |
| 8 | 108 | 2024-03-04 12:00:00 | ios |
| 9 | 109 | 2024-03-05 09:15:00 | web |
| 10 | 110 | 2024-03-05 09:45:00 | ios |
| 11 | 101 | 2024-03-01 07:46:43 | samsung |
| 12 | 105 | 2024-03-05 06:05:56 | android |
| 13 | 111 | 2024-03-04 03:59:12 | opera |
| 14 | 111 | 2024-03-04 15:30:33 | opera |
| 15 | 104 | 2024-02-29 14:24:29 | null |
| 16 | 102 | 2024-03-06 13:30:09 | unknown |
| 17 | 103 | 2024-03-02 09:59:59 | samsung |
| 18 | 103 | 2024-02-29 13:57:13 | samsung |
| 19 | 106 | 2024-03-04 01:08:05 | pixel |
| 20 | 106 | 2024-03-03 04:46:26 | firefox |
| 21 | 102 | 2024-02-29 13:07:47 | null |
| 22 | 101 | 2024-03-02 23:36:53 | mobile_web |
| 23 | 102 | 2024-03-03 09:31:32 | samsung |
| 24 | 108 | 2024-03-02 05:32:47 | console |
| 25 | 103 | 2024-03-02 05:55:04 | null |
| 26 | 109 | 2024-03-02 14:25:41 | desktop |
| 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 | |
| 8 | 108 | 109 | 2024-03-04 08:00:00 | |
| 4 | 104 | 105 | 2024-03-02 12:00:00 | |
| 10 | 110 | 111 | 2024-03-05 13:00:00 | |
| 5 | 105 | 106 | 2024-03-03 14:20:00 | 2024-03-04 08:00:00 |
| 9 | 109 | 110 | 2024-03-04 16:45:00 | |
| 7 | 107 | 108 | 2024-03-05 07:50:00 | 2024-03-06 09:00:00 |
| 11 | 100 | 103 | 2024-03-06 21:02:26 | 2024-03-07 04:50:29 |
| 12 | 105 | 107 | 2024-03-07 13:11:03 | 2024-03-08 17:57:01 |
| 13 | 101 | 109 | 2024-03-01 09:09:47 | 2024-03-01 08:20:50 |
| 14 | 101 | 105 | 2024-02-28 20:16:15 | 2024-03-01 03:13:24 |
| 15 | 105 | 112 | 2024-03-01 06:15:25 | 2024-03-05 13:51:27 |
| 16 | 100 | 109 | 2024-02-27 13:47:14 | null |
| 17 | 109 | 107 | 2024-02-29 08:20:38 | null |
| 18 | 108 | 101 | 2024-03-03 11:55:45 | 2024-03-04 05:56:05 |
| 19 | 110 | 109 | 2024-03-05 18:31:12 | 2024-02-27 18:34:35 |
| 20 | 104 | 112 | 2024-03-03 06:28:48 | 2024-02-29 02:14:36 |
| 21 | 106 | 105 | 2024-03-06 13:14:53 | 2024-02-27 23:55:49 |
| 22 | 102 | 103 | 2024-03-01 09:55:17 | null |
| 23 | 110 | 101 | 2024-03-05 01:47:37 | 2024-03-07 22:38:54 |
| 24 | 104 | 110 | 2024-03-06 09:38:41 | 2024-02-27 20:21:05 |
| 25 | 108 | 112 | 2024-03-04 17:30:21 | 2024-03-07 23:31:21 |
| 26 | 106 | 103 | 2024-03-01 16:16:12 | null |
| 27 | 111 | 111 | 2024-03-05 10:37:40 | 2024-03-09 00:06:39 |
| 28 | 100 | 109 | 2024-03-01 05:56:29 | 2024-03-04 20:28:08 |
| request_date | requests_sent | requests_accepted | acceptance_rate |
|---|---|---|---|
| 2024-02-27 | 1 | 0 | 0.00 |
| 2024-02-28 | 1 | 1 | 1.00 |
| 2024-02-29 | 1 | 0 | 0.00 |
| 2024-03-01 | 7 | 4 | 0.57 |
| 2024-03-02 | 2 | 1 | 0.50 |
| 2024-03-03 | 4 | 4 | 1.00 |
| 2024-03-04 | 3 | 1 | 0.33 |
| 2024-03-05 | 5 | 4 | 0.80 |
| 2024-03-06 | 3 | 3 | 1.00 |
| 2024-03-07 | 1 | 1 | 1.00 |
