I'm using AWS QuickSight to build a dashboard with analytics and metrics related to the usage of a system. I'm trying to visualize user's registration over time. I've created a parameter and control on my dashboard that allows the dashboard user to select 'Last N days' (7, 30, 60, 90, 180, 365 days), and I have an associated line chart that will plot the related data.
However the issue is that there are some days where no user's registered, and that leaves gaps of seemingly unreported data (in the line chart). What I would like to do is JOIN my current query on day with a query that returns a single field each row containing the last 365 days.
Select count (DISTINCT id), date_trunc('day', created_at) as day
FROM users
GROUP BY day
ORDER BY day desc
To get date instead of numbers you can use below query:
Query:
with recursive date_range(day,daycount) as
(
SELECT '1 Jan 2020'::date as DAY, 1 as daycount
UNION ALL
SELECT day+1, daycount+1 from date_range WHERE daycount<365
)select day from date_range
Output:
| day |
| :--------- |
| 2020-01-01 |
| 2020-01-02 |
| 2020-01-03 |
.
.
.
.
| 2020-12-28 |
| 2020-12-29 |
| 2020-12-30 |
db<fiddle here
You can use recursive common table expression to generate that. Then you just can join that cte with your table. Please check out below code.
with recursive date_range(day) as
(
SELECT 1 as day
UNION ALL
SELECT day+1
from date_range
WHERE day < 365
)select DATE_TRUNC('day', NOW() - concat(day,' days')::interval ) as date from date_range
Output:
|date |
------------------------
|2021-06-10 00:00:00+01|
|2021-06-09 00:00:00+01|
|2021-06-08 00:00:00+01|
|2021-06-07 00:00:00+01|
|2021-06-06 00:00:00+01|
|2021-06-05 00:00:00+01|
|2021-06-04 00:00:00+01|
|2021-06-03 00:00:00+01|
|2021-06-02 00:00:00+01|
|2021-06-01 00:00:00+01|
...
db<fiddle here