Hi,
I'm using BigQuery as the data source for Metabase. I've blow query with date field filter -
SELECT
(FORMAT_TIMESTAMP('%Y-%m', dbt.intermediate_net_bookings.day )) AS net_bookings_date_month,
COALESCE(SUM(dbt.intermediate_net_bookings.net_bookings ), 0) AS net_bookings_sum,
SUM(COALESCE(SUM(dbt.intermediate_net_bookings.net_bookings ), 0)) over() AS total_bookings_sum
FROM dbt.intermediate_net_bookings
where {{date_1}}
GROUP BY
1
ORDER BY
1 DESC
LIMIT 2000
Metabase converted above query as below -
SELECT
(FORMAT_TIMESTAMP('%Y-%m', dbt.intermediate_net_bookings.day )) AS net_bookings_date_month,
COALESCE(SUM(dbt.intermediate_net_bookings.net_bookings ), 0) AS net_bookings_sum,
SUM(COALESCE(SUM(dbt.intermediate_net_bookings.net_bookings ), 0)) over() AS total_bookings_sum
FROM dbt.intermediate_net_bookings
where timestamp_trunc(dbt.intermediate_net_bookings.day, day) BETWEEN timestamp "2023-04-01 00:00:00Z"
AND timestamp "2023-04-30 00:00:00Z"
GROUP BY
1
ORDER BY
1 DESC
LIMIT 2000
The issue is while running above query with date field filer for past one month is take lot of time(>15 minutes and fails after that) whereas if I change above query with hardcoded date it runs quickly ~3 seconds.
Below is the hardcoded query which runs faster -
SELECT
(FORMAT_TIMESTAMP('%Y-%m', dbt.intermediate_net_bookings.day )) AS net_bookings_date_month,
COALESCE(SUM(dbt.intermediate_net_bookings.net_bookings ), 0) AS net_bookings_sum,
SUM(COALESCE(SUM(dbt.intermediate_net_bookings.net_bookings ), 0)) over() AS total_bookings_sum
FROM dbt.intermediate_net_bookings
where date(dbt.intermediate_net_bookings.day) BETWEEN date("2023-04-01") AND date("2023-04-30")
GROUP BY
1
ORDER BY
1 DESC
LIMIT 2000
Can someone help what changes I need to do to make Metabase past the date field filter with proper date instead of timestamp?
Thanks is advance.