TheBuilderJR commented on issue #13957:
URL: https://github.com/apache/datafusion/issues/13957#issuecomment-2566711104
I figured out how to do this via range and unnest
```
dates AS (
SELECT
date_trunc('${timeCoersion}', unnest(range(
TO_TIMESTAMP(${start}),
TO_TIMESTAMP(${end}),
INTERVAL '1 ${timeCoersion}'
))) AS ts_date
),
```
For posterity this was my full query
```
WITH
hits AS (
SELECT
date_trunc('${timeCoersion}', timestamp_utc) AS ts_date,
session_id,
COUNT(*) AS pageviews,
CASE WHEN COUNT(*) = 1 THEN 1 ELSE 0 END AS is_bounce,
MAX(timestamp_utc) AS latest_hit_aux,
MIN(timestamp_utc) AS first_hit_aux
FROM ${"_" + token.replace(/-/g, "_")}
WHERE
${processFilters(filters, useTelemetry)}
timestamp_utc >= TO_TIMESTAMP(${start})
AND timestamp_utc <= TO_TIMESTAMP(${end})
GROUP BY
date_trunc('${timeCoersion}', timestamp_utc),
session_id
),
dates AS (
SELECT
date_trunc('${timeCoersion}', unnest(range(
TO_TIMESTAMP(${start}),
TO_TIMESTAMP(${end}),
INTERVAL '1 ${timeCoersion}'
))) AS ts_date
),
data AS (
SELECT
ts_date,
COUNT(DISTINCT session_id) AS visits,
SUM(pageviews) AS pageviews,
ROUND((SUM(is_bounce)::FLOAT / NULLIF(COUNT(DISTINCT
session_id), 0)) * 100, 2) AS bounce_rate,
ROUND(AVG(EXTRACT(EPOCH FROM (latest_hit_aux -
first_hit_aux))), 2) AS avg_session_sec
FROM hits
GROUP BY ts_date
)
SELECT
d.ts_date as date,
COALESCE(h.visits, 0) as visits,
COALESCE(h.pageviews, 0) as pageviews,
COALESCE(h.bounce_rate, 0) as bounce_rate,
COALESCE(h.avg_session_sec, 0) as avg_session_sec
FROM dates d
LEFT JOIN data h ON d.ts_date = h.ts_date
ORDER BY d.ts_date
```
Thanks y'all!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
