Hello,
I have a single table with multiple datetime columns that represent when a row in my table transitioned through various events. A simplified example of this table would look like:
id | created_at | registered_at | downloaded_at
12345 | 2020-01-01 | 2020-01-02 | 2020-01-03
67890 | 2020-01-02 | 2020-01-03 | 2020-01-04
In practice each of those dates is a full timestamp, but I would truncate them down to dates for purposes of this visualization. I want to build a table visualization that shows counts of each event per day like so:
Date | # Created | # Registered | # Downloaded
2020-01-01 | 1 | 0 | 0
2020-01-02 | 1 | 1 | 0
2020-01-03 | 0 | 1 | 1
2020-01-04 | 0 | 0 | 1
I’m unsure what the best way to go about modeling this data in Looker would be. I imagine I need something like a derived table that generates every date and then use this to join on the three date columns in my event table? I’m fairly new to Looker and not sure exactly what that might look like.
Thanks in advance for any recommendations!
Hello @Thogan and welcome here!
For your use case you can use a derived table that generates on row per day then join that table 3 times but maybe there is a better way.
You could try using a union all like:
with creation as (
select
disinct
id as id,
'created' as event_type
created_at::date as event_date
from yourtable
where created_at is not null
),
registration as (
select
disinct
id as id,
'registered' as event_type
registered_at::date as event_date
from yourtable
where registered_at is not null
),
download as (
select
disinct
id as id,
'downloaded' as event_type
downloaded_at::date as event_date
from yourtable
where downloaded_at is not null
),
final as (
select * from creation
union all
select * from registration
union all
select * from download
)
select * from final
Then from here you could just create filtered counts of the distinct IDs for each event_type.
Not sure that’s the right approach though if it’s a large table that needs to be refreshed quit often.