3kt 3kt - 4 months ago 8
SQL Question

Find particular value in subquery result

I have an event tracking table, that links events to the users that where impacted by those. For a particular

event_id
corresponds several
user_id
. Each event_tracking element also has an
event_wave
number.

I would like, for a specific event, to extract the number of users that were impacted by it, the maximum event wave (1), and also know if a particular user was impacted by the event (2).

Separately I can do (1) using :

SELECT COUNT(*), MAX(event_wave)
FROM event_tracking
WHERE event_id = 123


And (2) using :

SELECT 456 = ANY(SELECT user_id FROM event_tracking WHERE event_id = 123)


However, is it possible to have those results (1&2) without using 2 subqueries ?

Answer

Example data:

create table event_tracking (event_id int, user_id int, event_wave int);
insert into event_tracking values
(123, 10, 3),
(123, 20, 2),
(123, 30, 1);

If you want to get all users for the event_id = 123:

select count(*), max(event_wave), array_agg(user_id) users
from event_tracking
where event_id = 123;

 count | max |   users    
-------+-----+------------
     3 |   3 | {10,20,30}
(1 row)

If you want the user for the event_id = 123 with max event_wave:

select distinct on (event_id) 
    row_number() over (order by event_wave) as count,
    event_wave as max, 
    user_id
from event_tracking
where event_id = 123
order by event_id, event_wave desc;

 count | max | user_id 
-------+-----+---------
     3 |   3 |      10
(1 row)

If you want to have simple yes/no for a given user_id:

select count(*), max(event_wave), 456 = any(array_agg(user_id)) yesno
from event_tracking
where event_id = 123;

or

select distinct on (event_id) 
    row_number() over (order by event_wave) as count,
    event_wave as max, 
    user_id = 456 yesno
from event_tracking
where event_id = 123
order by event_id, event_wave desc;