Table Visualization for Daily Counts

[Thogan]

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!

[Cyril_MTL_Analy]

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.