Account Activity Summary
For every account, show the number of users, the total number of events, and the dates of the first and last event. Include accounts whose users have no events.
Expected output
account_id, company, users, events, first_event_date, last_event_date. Sorted by account_id.
Row order is checked. Column names are not checked; numbers are compared to 2 decimals.
Dataset: B2B SaaS product events
A project-management SaaS: company accounts, their users, product event logs (Jan to Apr 2026) and subscription history.
accounts
One row per customer company.
| account_id | INTEGER | primary key |
| company | TEXT | |
| industry | TEXT | |
| country | TEXT | |
| created_at | TEXT | YYYY-MM-DD |
users
One row per user. Every user belongs to one account.
| user_id | INTEGER | primary key |
| account_id | INTEGER | |
| signup_date | TEXT | YYYY-MM-DD, same day as the user's signup event |
| acquisition_channel | TEXT | organic, paid_search, referral or partner |
| country | TEXT |
events
Product event log. Data ends on 2026-04-30.
| event_id | INTEGER | primary key, increases with event_time |
| user_id | INTEGER | |
| event_name | TEXT | signup, login, view_dashboard, export_report, create_project, invite_teammate, upgrade_click |
| event_time | TEXT | YYYY-MM-DD HH:MM:SS (UTC) |
subscriptions
Paid subscription periods per account. An account with no rows is on the free plan.
| subscription_id | INTEGER | primary key |
| account_id | INTEGER | |
| plan | TEXT | team or business |
| mrr | REAL | monthly recurring revenue in USD |
| started_at | TEXT | YYYY-MM-DD, inclusive |
| ended_at | TEXT | YYYY-MM-DD, exclusive; NULL while active. May be in the future for a scheduled cancellation. |
Engine: SQLite. Dates are text (YYYY-MM-DD); use strftime(), date() and julianday() for date math.
Related problems
- Revenue by Product Category (easy)
- Customers Who Never Ordered (easy)
- Monthly Revenue Trend (easy)
- Average Order Value by Country (easy)
- Repeat Purchase Rate (medium)