Adding measure based on date

[Arturo_Candau]

Hi all,

I am trying to create an aggregation based on the date of a measure I have which should not include the date in the grouping. Basically implementing the query below in Looker.

cte as (

select user, item, count(purchase_id) purchase_count, min(date) date_, sum(price) sum_amount

from purchases

group by 1, 2

),

daily_user_type_count as (

select

date_trunc('day', date_) date_,

case

when purchase_count = 1 then 'unique'

when purchase_count >1 then 'multiple'

end user_type,

sum(sum_amount) sum_amount, count(user) count_

from cte

group by 1, 2

)

Is there a way to implement this without a derived_table?