runtimetask Join fan-out & cardinality · hard
Debug: sessions and events per workspace
For workspaces 1–40, return account_id, sessions and events (distinct event_id) started or occurring in March 2024, 0 when none. The current query multiplies sessions by events. Fix it.
what to return
- Columns
account_idsessionseventsin this order- Rows
- 40 rows · any order
- Skill
- Combine measures from two child tables without multiplying them. Refresher: Revenue & payment reconciliation →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result40 rows · any order
| account_id | sessions | events |
|---|---|---|
| 1 | 4 | 12 |
| 2 | 0 | 0 |
| 3 | 4 | 11 |
first 3 of 40 rows
data you can query
Product / SaaS Analytics dataset: the first 6 rows of each table. Under each column name: the kind of value it holds, and which table it links to. Your queries run on all rows.
accounts7 columns · 3,200 rows
| account_idnumber · unique id | account_nametext | industrytext | company_sizetext | countrytext | acquisition_channeltext | created_atdate & time |
|---|---|---|---|---|---|---|
| 1 | Iron Capital Inc | Logistics | 11-50 | United States | organic | 2024-01-14 18:52:14 |
| 2 | Vector Data Co | Nonprofit | 51-200 | United Kingdom | social | 2024-01-15 13:21:52 |
| 3 | True Foods Inc | Healthcare | 201-1000 | United States | outbound_sales | 2024-01-19 11:22:02 |
| 4 | Meridian Works | Software | 11-50 | United States | organic | 2024-01-23 23:42:36 |
| 5 | Prism Health Group | Software | 1-10 | Germany | referral | 2024-01-25 11:43:43 |
| 6 | Beacon Supply Ltd | Media | 1000+ | United Kingdom | organic | 2024-01-27 20:19:28 |
- account_idnumber · unique id
- 1
- account_nametext
- Iron Capital Inc
- industrytext
- Logistics
- company_sizetext
- 11-50
- countrytext
- United States
- acquisition_channeltext
- organic
- created_atdate & time
- 2024-01-14 18:52:14
- account_idnumber · unique id
- 2
- account_nametext
- Vector Data Co
- industrytext
- Nonprofit
- company_sizetext
- 51-200
- countrytext
- United Kingdom
- acquisition_channeltext
- social
- created_atdate & time
- 2024-01-15 13:21:52
- account_idnumber · unique id
- 3
- account_nametext
- True Foods Inc
- industrytext
- Healthcare
- company_sizetext
- 201-1000
- countrytext
- United States
- acquisition_channeltext
- outbound_sales
- created_atdate & time
- 2024-01-19 11:22:02
- account_idnumber · unique id
- 4
- account_nametext
- Meridian Works
- industrytext
- Software
- company_sizetext
- 11-50
- countrytext
- United States
- acquisition_channeltext
- organic
- created_atdate & time
- 2024-01-23 23:42:36
- account_idnumber · unique id
- 5
- account_nametext
- Prism Health Group
- industrytext
- Software
- company_sizetext
- 1-10
- countrytext
- Germany
- acquisition_channeltext
- referral
- created_atdate & time
- 2024-01-25 11:43:43
- account_idnumber · unique id
- 6
- account_nametext
- Beacon Supply Ltd
- industrytext
- Media
- company_sizetext
- 1000+
- countrytext
- United Kingdom
- acquisition_channeltext
- organic
- created_atdate & time
- 2024-01-27 20:19:28
first 6 of 3,200 rows · your queries use all of them
users9 columns · 11,348 rows
| user_idnumber · unique id | account_idnumber · links to accounts | full_nametext | emailtext | roletext | signup_sourcetext | referred_by_user_idnumber · links to users | created_atdate & time | deactivated_attext |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 | Omar Jackson | omar.jackson@ironcapital1.example | owner | workspace_creator | NULL | 2024-01-14 18:52:14 | NULL |
| 2 | 1 | Imani Kang | imani.kang@ironcapital1.example | admin | invitation | NULL | 2024-01-24 03:43:14 | NULL |
| 3 | 1 | Chloe Mitchell | chloe.mitchell@ironcapital1.example | admin | invitation | NULL | 2024-02-02 21:34:25 | NULL |
| 4 | 1 | Jaya Carter | jaya.carter@ironcapital1.example | member | domain_join | NULL | 2024-03-31 03:39:32 | NULL |
| 5 | 1 | Zoe Turner | zoe.turner@ironcapital1.example | member | domain_join | NULL | 2024-02-12 02:53:31 | NULL |
| 6 | 2 | Esther Smith | esther.smith@vectordata2.example | owner | workspace_creator | NULL | 2024-01-15 13:21:52 | NULL |
- user_idnumber · unique id
- 1
- account_idnumber · links to accounts
- 1
- full_nametext
- Omar Jackson
- emailtext
- omar.jackson@ironcapital1.example
- roletext
- owner
- signup_sourcetext
- workspace_creator
- referred_by_user_idnumber · links to users
- NULL
- created_atdate & time
- 2024-01-14 18:52:14
- deactivated_attext
- NULL
- user_idnumber · unique id
- 2
- account_idnumber · links to accounts
- 1
- full_nametext
- Imani Kang
- emailtext
- imani.kang@ironcapital1.example
- roletext
- admin
- signup_sourcetext
- invitation
- referred_by_user_idnumber · links to users
- NULL
- created_atdate & time
- 2024-01-24 03:43:14
- deactivated_attext
- NULL
- user_idnumber · unique id
- 3
- account_idnumber · links to accounts
- 1
- full_nametext
- Chloe Mitchell
- emailtext
- chloe.mitchell@ironcapital1.example
- roletext
- admin
- signup_sourcetext
- invitation
- referred_by_user_idnumber · links to users
- NULL
- created_atdate & time
- 2024-02-02 21:34:25
- deactivated_attext
- NULL
- user_idnumber · unique id
- 4
- account_idnumber · links to accounts
- 1
- full_nametext
- Jaya Carter
- emailtext
- jaya.carter@ironcapital1.example
- roletext
- member
- signup_sourcetext
- domain_join
- referred_by_user_idnumber · links to users
- NULL
- created_atdate & time
- 2024-03-31 03:39:32
- deactivated_attext
- NULL
- user_idnumber · unique id
- 5
- account_idnumber · links to accounts
- 1
- full_nametext
- Zoe Turner
- emailtext
- zoe.turner@ironcapital1.example
- roletext
- member
- signup_sourcetext
- domain_join
- referred_by_user_idnumber · links to users
- NULL
- created_atdate & time
- 2024-02-12 02:53:31
- deactivated_attext
- NULL
- user_idnumber · unique id
- 6
- account_idnumber · links to accounts
- 2
- full_nametext
- Esther Smith
- emailtext
- esther.smith@vectordata2.example
- roletext
- owner
- signup_sourcetext
- workspace_creator
- referred_by_user_idnumber · links to users
- NULL
- created_atdate & time
- 2024-01-15 13:21:52
- deactivated_attext
- NULL
first 6 of 11,348 rows · your queries use all of them
sessions5 columns · 47,891 rows
| session_idnumber · unique id | user_idnumber · links to users | started_atdate & time | ended_atdate & time | devicetext |
|---|---|---|---|---|
| 1 | 1 | 2024-01-14 18:52:14 | 2024-01-14 19:01:14 | web |
| 2 | 1 | 2024-01-21 11:24:15 | 2024-01-21 11:37:15 | web |
| 3 | 2 | 2024-01-24 03:43:14 | 2024-01-24 03:46:14 | android |
| 4 | 1 | 2024-02-02 07:45:53 | 2024-02-02 08:22:53 | web |
| 5 | 3 | 2024-02-02 21:34:25 | 2024-02-02 21:49:25 | web |
| 6 | 4 | 2024-03-31 03:39:32 | 2024-03-31 03:44:32 | web |
- session_idnumber · unique id
- 1
- user_idnumber · links to users
- 1
- started_atdate & time
- 2024-01-14 18:52:14
- ended_atdate & time
- 2024-01-14 19:01:14
- devicetext
- web
- session_idnumber · unique id
- 2
- user_idnumber · links to users
- 1
- started_atdate & time
- 2024-01-21 11:24:15
- ended_atdate & time
- 2024-01-21 11:37:15
- devicetext
- web
- session_idnumber · unique id
- 3
- user_idnumber · links to users
- 2
- started_atdate & time
- 2024-01-24 03:43:14
- ended_atdate & time
- 2024-01-24 03:46:14
- devicetext
- android
- session_idnumber · unique id
- 4
- user_idnumber · links to users
- 1
- started_atdate & time
- 2024-02-02 07:45:53
- ended_atdate & time
- 2024-02-02 08:22:53
- devicetext
- web
- session_idnumber · unique id
- 5
- user_idnumber · links to users
- 3
- started_atdate & time
- 2024-02-02 21:34:25
- ended_atdate & time
- 2024-02-02 21:49:25
- devicetext
- web
- session_idnumber · unique id
- 6
- user_idnumber · links to users
- 4
- started_atdate & time
- 2024-03-31 03:39:32
- ended_atdate & time
- 2024-03-31 03:44:32
- devicetext
- web
first 6 of 47,891 rows · your queries use all of them
events8 columns · 135,428 rows
| delivery_idnumber · unique id | event_idtext | user_idnumber · links to users | account_idnumber · links to accounts | session_idnumber · links to sessions | event_nametext | occurred_atdate & time | received_atdate & time |
|---|---|---|---|---|---|---|---|
| 1 | evt_0000001 | 1 | 1 | 1 | user_signed_up | 2024-01-14 18:52:14 | 2024-01-14 18:52:33 |
| 2 | evt_0000002 | 1 | 1 | 1 | onboarding_started | 2024-01-14 18:52:51 | 2024-01-14 18:53:06 |
| 3 | evt_0000084 | 6 | 2 | 32 | user_signed_up | 2024-01-15 13:21:52 | 2024-01-15 13:22:00 |
| 4 | evt_0000085 | 6 | 2 | 32 | onboarding_started | 2024-01-15 13:22:05 | 2024-01-15 13:22:16 |
| 5 | evt_0000086 | 6 | 2 | 32 | onboarding_completed | 2024-01-15 13:23:52 | 2024-01-15 13:24:12 |
| 6 | evt_0000019 | 1 | 1 | 9 | search_performed | 2024-01-17 20:56:55 | 2024-01-17 20:56:59 |
- delivery_idnumber · unique id
- 1
- event_idtext
- evt_0000001
- user_idnumber · links to users
- 1
- account_idnumber · links to accounts
- 1
- session_idnumber · links to sessions
- 1
- event_nametext
- user_signed_up
- occurred_atdate & time
- 2024-01-14 18:52:14
- received_atdate & time
- 2024-01-14 18:52:33
- delivery_idnumber · unique id
- 2
- event_idtext
- evt_0000002
- user_idnumber · links to users
- 1
- account_idnumber · links to accounts
- 1
- session_idnumber · links to sessions
- 1
- event_nametext
- onboarding_started
- occurred_atdate & time
- 2024-01-14 18:52:51
- received_atdate & time
- 2024-01-14 18:53:06
- delivery_idnumber · unique id
- 3
- event_idtext
- evt_0000084
- user_idnumber · links to users
- 6
- account_idnumber · links to accounts
- 2
- session_idnumber · links to sessions
- 32
- event_nametext
- user_signed_up
- occurred_atdate & time
- 2024-01-15 13:21:52
- received_atdate & time
- 2024-01-15 13:22:00
- delivery_idnumber · unique id
- 4
- event_idtext
- evt_0000085
- user_idnumber · links to users
- 6
- account_idnumber · links to accounts
- 2
- session_idnumber · links to sessions
- 32
- event_nametext
- onboarding_started
- occurred_atdate & time
- 2024-01-15 13:22:05
- received_atdate & time
- 2024-01-15 13:22:16
- delivery_idnumber · unique id
- 5
- event_idtext
- evt_0000086
- user_idnumber · links to users
- 6
- account_idnumber · links to accounts
- 2
- session_idnumber · links to sessions
- 32
- event_nametext
- onboarding_completed
- occurred_atdate & time
- 2024-01-15 13:23:52
- received_atdate & time
- 2024-01-15 13:24:12
- delivery_idnumber · unique id
- 6
- event_idtext
- evt_0000019
- user_idnumber · links to users
- 1
- account_idnumber · links to accounts
- 1
- session_idnumber · links to sessions
- 9
- event_nametext
- search_performed
- occurred_atdate & time
- 2024-01-17 20:56:55
- received_atdate & time
- 2024-01-17 20:56:59
first 6 of 135,428 rows · your queries use all of them
solution.sql
Hints and the answer are always available. Using them counts the question as solved with help.