Column into multiple columns with distinct count

Question

I have a table which looks like:

event_date	event_name	user_id
20220407	n1	a
20220407	n2	b
20220407	n3	a
20220408	n1	a
20220408	n1	a
20220408	n2	c

Each row is presenting single event with params (it’s actually a bigquery table with data from firebase)

I want to select only needed events and place their sum for distinct users grouped by day into another table, like this:

date	n1 distinct users count	n2 distinct users count
20220407	1	1
20220408	2	0

I've tried smth like:

SELECT COUNT (DISTINCT user_pseudo_id) as users
,event_date 
event_name,  
case app_info.id when 'com.kaspersky.standalone-vpn' then 'KSeC-iOS'  
                    when  'com.kaspersky.secure.connection' then 'KSeC-Android'  
                    when  'com.kaspersky.securityadvisor' then 'KSC-iOS'  
                    when  'com.kaspersky.security.cloud' then 'KSC-Android'                      
                    else app_info.id end as product 
, SUBSTRING(device.language, 1, 2) as language  
, geo.country  
, app_info.version as app_version  
  FROM `ksec-android.analytics_156657667.events_*`
  WHERE (event_name = 'first_open' OR event_name = 'user_engagement' OR 'event_name' = 'app_remove') 
  and _table_suffix >= FORMAT_DATE("%Y%m%d",(date_sub(CURRENT_DATE(), interval 1 day)))
  group by event_date 
  ,product 
  ,language 
  ,country 
  ,app_version
  ,event_name
) src
pivot
(
  count(users)
  for event_name in ([first_open], [user_engagement], [app_remove])
) piv
group by event_date 
  ,product 
  ,language 
  ,country 
  ,app_version

I really don’t get it, would be so thankful for help

Answer 1

consider below approach

select * from your_table 
pivot (count(distinct user_id) as count for event_name in ('n1', 'n2'))

if applied to sample data in your question - output is