I have a table 'exports' which looks like the following:

I want to calculate an output which produces:

The total count of events by user along with their last export design category and max date

The expected output would be the following:

This is what I have so far:

SELECT distinct 
     a.user_id
    ,count(a.event_id) as Count_events
FROM exported as a
inner join 
(
select 
     max(timestamp) as last_export_date
    ,user_id
    ,design_cateogry
    ,count(event_id) as event_count_2
from exported as b
    group by user_id, design_category
)as c
on a.user_id = c.user_id 
group by a.user_id

CodePudding user response：

If you only wanted to select the most recent row, you could try:

SELECT *
FROM exports
QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id, design_category 
     ORDER BY timestamp DESC) = 1

However it looks like you also want to aggregate.

You could combine your aggregate with "last known value" with something like this:

SELECT
 user_id,
 LAST_VALUE('timestamp' ignore NULLS) OVER (PARTITION BY user_id ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS LATEST_EXPORT_DATE, 
 LAST_VALUE(design_category ignore NULLS) OVER (PARTITION BY user_id ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS LATEST_DESIGN,
COUNT(1) AS count_events
FROM exports
GROUP BY 1,2,3

Edit: The syntax might be different depending on your RDBMS. I got this syntax from Rasgo which automatically translates into SQL for you. I used snowflake syntax.

CodePudding user response：

I like @Josh answer but you could also use windowing functions. here is the fiddle https://www.db-fiddle.com/f/44CMtdjjir4FwtF2y4TXf2/0

with t as (
  select 
    *, 
    RANK() OVER (
      PARTITION BY user_id 
      ORDER BY 
        timestamp DESC
    ) rnk, 
    count('Dracula') over (partition by user_id) as count_events 
  from 
    exported
) 
select 
  user_id, 
  count_events, 
  design_category, 
  timestamp 
from 
  t 
where 
  rnk = 1 
order by 
  user_id desc;