CODEWITHSUNDEEP
sqlpostgresql
Sidi Chang
Sidi Chang

Reputation: 25

Get aggregated result daily in SQL

I would need to handle a very complex SQL problem: the input data looks like:

device_id | install_time

AND I would need to get daily installs and re_installs(means this device has already had a install_time before today), how could I quickly generate this table result?

Here is a sample result:

id1 | 08/24/2020
id1 | 08/23/2020
id1 | 08/22/2020
id2 | 08/22/2020
id3 | 08/23/2020
id3 | 08/22/2020

the result should be

date | install | re_install
08/22/2020 | 3 | 0
08/23/2020 | 2 | 2
08/24/2020 | 1 | 1

Upvotes: 0

Views: 31

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269723

If I understand correctly, you can use window functions and aggregation:

select install_time::date,
       count(*) as num_installs,
       count(*) filter (where seqnum > 1) as num_reinstalls
from (select t.*,
             row_number() over (partition by device_id order by install_time) as seqnum
      from t
     ) t
group by install_time::date

Upvotes: 1

Related Questions