Dynamic Category by Dynamic Date Grouping

Agustina · October 19, 2023, 2:25pm

Hello guys!

I have a table with the following structure:

account_id | event_date            | event_name | event_quantity | active_day | inactive_day | pilot_day
ABC1            | 2023-10-01         | monitor          | 4                                |  1                     | 0                          | 0
ABC1            | 2023-10-01         | action              | 2                                |  1                     | 0                          | 0
ABC1            | 2023-10-01         | transaction  | 1                                |  1                     | 0                          | 0
HGB4            | 2023-10-01         | monitor         | 0                                |  0                     | 1                          | 0
HGB4            | 2023-10-01         | transaction | 0                                |  0                     | 1                          | 0
HGB4            | 2023-10-01         | action             | 0                                |  0                     | 1                          | 0

We have 3 different account status(active, inactive, pilot) based on the amount of active, inactive and pilot days each account has.

I need to create a graph where I show the accounts per status per period.

The status should be calculated dinamically depending on the date range selected by the user and the period breakdown. For example, someone could select October 2023 as the date range, but "Week" as the period breakdown so he should see 4 bars with the quantity of accounts that were active, inactive and pilot for each respective week.

I've tried to create this with 2 questions, but didn't work as expected. Does someone know how to create something like this?

Thank you!

Agustina · October 20, 2023, 11:17am

If it's useful for someone out there, I solved it using 3 variables inside the SQL code of 2 questions.

QUESTION 1

SELECT
  distinct(account_id) as account,
  date_trunc( {{period}}, "date_field") as date_breakdown,
  CASE WHEN SUM(active_day) OVER (PARTITION BY account_id, date_trunc( {{period}}, "date_field")) > 0 THEN 'active' 
WHEN SUM(pilot_day) OVER (PARTITION BY  account_id, date_trunc( {{period}}, "date_field")) > 0 AND SUM(active_day) OVER (PARTITION BY account_id, date_trunc( {{period}}, "date_field" )) = 0 THEN 'pilot'
ELSE 'inactive' END as status

FROM database
where database.date_field >= {{start_date}} AND database.date_field <= {{end_date}}

start_date and end_date are single date variables. Period is a text variable with 3 options (week, month, year).

QUESTION 2

SELECT
  "source"."status" AS "status",
  DATE_TRUNC({{period}}, "source"."action_date") AS "action_date",
  count(distinct "source"."tenantid") AS "Tenants"
FROM
question1
GROUP BY
  status, date_trunc( {{period}}, "date_field"
ORDER BY
status ASC, date_trunc( {{period}}, "date_field") ASC

When I added the visualization in the dashboard, I included filter widgets for start date, end date and period and indicated which column each widget should filter.

This is the result

I can change the breakdown and see it by month