I am working with specific dates, for example, from 01/01/2022 to 12/31/2022, and the query works correctly from the Metabase editor. Now, since I need to make the query more complex, I use a CTE and run it in the SQL editor, but it tells me there are no results. It is not the first time, but it has already happened to me with other queries with different specific dates. In all cases, it tells me "No results!" but if I run the same query in the Metabase editor it works correctly.
This is one of my queries as an example:
WITH cumulative_hours_2022
AS
(
SELECT
dayofmonth(table.column1) AS spent_date
, sum(table.column2) AS cumulative_hours
FROM table
WHERE (table.column1 = 1
AND table.column1 >= convert_tz('2022-01-01 00:00:00.000', 'Europe/Oslo', @@session.time_zone)
AND table.column1 < convert_tz('2024-01-01 00:00:00.000', 'Europe/Oslo', @@session.time_zone))
GROUP BY dayofmonth(table.column1)
ORDER BY dayofmonth(table.column1) ASC
)
SELECT
spent_date
, cumulative_hours
, RANK() OVER (ORDER BY spent_date) AS row_num
FROM cumulative_hours_2022
ORDER BY row_num DESC
What would the problem be in this case?