Add date controls to UBI dashboard

[thalescb]

Date Controls Without Breaking Scorecards
Goal: Let users filter charts by date while keeping scorecards (DAU/WAU/MAU) showing latest data.

Solution:
Use Dune’s Dashboard Parameters to create dynamic date filters that only apply to specific visualizations:

Create Parameters:

{{Time Range}} (Dropdown: Last 7/30/90 days, Custom)

{{Start Date}}, {{End Date}} (Date pickers, shown when "Custom" is selected).

Modify Queries for Charts:
Add a WHERE clause to filter data based on parameters:

sql
Copy
WHERE day >= CASE
WHEN '{{Time Range}}' = '7' THEN CURRENT_DATE - INTERVAL '7' DAY
WHEN '{{Time Range}}' = '30' THEN CURRENT_DATE - INTERVAL '30' DAY
WHEN '{{Time Range}}' = '90' THEN CURRENT_DATE - INTERVAL '90' DAY
ELSE '{{Start Date}}'
END
AND day <= CASE
WHEN '{{Time Range}}' = 'Custom' THEN '{{End Date}}'
ELSE CURRENT_DATE
END
Keep Scorecards Unfiltered:
Use a separate query without date filters for DAU/WAU/MAU:

sql
Copy
-- Scorecard Query (unfiltered)
SELECT
COUNT(DISTINCT claimer) FILTER (WHERE day >= CURRENT_DATE - 1 DAY) AS dau,
COUNT(DISTINCT claimer) FILTER (WHERE day >= CURRENT_DATE - 7 DAY) AS wau,
COUNT(DISTINCT claimer) FILTER (WHERE day >= CURRENT_DATE - 30 DAY) AS mau
FROM daily_activity_table;
Result:
Scorecards: Always show the latest 1/7/30-day metrics.

Charts: Dynamically adjust to user-selected dates.