Description
Write a SQL query to find the number of activities for each user. Return user_id and activity_count. **Output columns (in order):** user_id, activity_count
Table:
activities| Column | Type |
|---|---|
| user_id | INT |
| activity_type | TEXT |
| activity_date | TEXT |
Examples
Input:
CREATE TABLE activities (user_id INT, activity_type TEXT, activity_date TEXT); INSERT INTO activities VALUES (1, 'login', '2024-01-01'), (1, 'purchase', '2024-01-01'), (1, 'logout', '2024-01-01'), (2, 'login', '2024-01-02'), (2, 'logout', '2024-01-02');Output:
1|3
2|2Explanation:
User 1 has 3 activities (login, purchase, logout). User 2 has 2 activities (login, logout).
Input:
CREATE TABLE activities (user_id INT, activity_type TEXT, activity_date TEXT); INSERT INTO activities VALUES (3, 'view_profile', '2024-01-15'), (4, 'comment', '2024-01-16'), (4, 'like', '2024-01-16'), (4, 'share', '2024-01-16'), (4, 'comment', '2024-01-17'), (5, 'upload', '2024-01-18');Output:
3|1
4|4
5|1Explanation:
User 3: 1 row (view_profile) = count 1. User 4: 4 rows (comment on 01-16, like on 01-16, share on 01-16, comment on 01-17) = count 4. User 5: 1 row (upload) = count 1. Each activity row is counted regardless of type.
Input:
CREATE TABLE activities (user_id INT, activity_type TEXT, activity_date TEXT); INSERT INTO activities VALUES (10, 'search', '2024-02-01'), (10, 'search', '2024-02-01'), (10, 'search', '2024-02-02'), (11, 'message', '2024-02-03'), (11, 'call', '2024-02-03'), (12, 'bookmark', '2024-02-04'), (12, 'bookmark', '2024-02-05'), (12, 'download', '2024-02-06');Output:
10|3
11|2
12|3Explanation:
User 10: 3 searches (02-01, 02-01, 02-02) = count 3. User 11: message + call on 02-03 = count 2. User 12: bookmark (02-04) + bookmark (02-05) + download (02-06) = count 3. COUNT(*) counts all rows per user_id group.
Constraints
- •
Use COUNT and GROUP BY