I am trying to implement the new Time Grouping feature in SQL with joins. Instead of using date field directly, I am using {{date_group}} and assigning it the type Time Grouping (Month & Week of the Year). As per my knowledge, time grouping variables are replaced with date_trunc and the grain is defined by the type selected in the filter. But I am catching an error while trying to do so. Need assistance in fixing this.
The errors I receive:
- ERROR: syntax error at or near "AS" Position: 6640
- facing same error
ERROR: missing FROM-clause entry for table "curated_leaflet_data" Position: 1487
Query:
WITH monthly_totals AS (
SELECT
{{date_group}},
COUNT() as total_count
FROM public.curated_data
WHERE 1=1
AND {{country}}
AND {{date}}
AND {{category}}
AND {{month}}
AND {{week}}
AND {{customer}}
-- Exclude: principle, brand, customer, sku, size filters
GROUP BY {{date_group}}
),
principle_counts AS (
SELECT
{{date_group}},
principle,
COUNT() as feature_count
FROM public.curated_data
WHERE 1=1
AND {{country}}
AND {{principle}}
AND {{date}}
AND {{customer}}
AND {{category}}
AND {{brand}}
AND {{sku}}
AND {{size}}
AND {{month}}
AND {{week}}
GROUP BY principle, {{date_group}}
)
SELECT
pc.{{date_group}},
pc.principle,
pc.truncated_start_date,
pc.feature_count,
mt.total_count as month_total_baseline,
ROUND(pc.feature_count * 100.0 / mt.total_count, 4) as true_market_share_percent
FROM principle_counts pc
JOIN monthly_totals mt ON pc.{{date_group}} = mt.{{date_group}}
GROUP BY pc.{{date_group}}
ORDER BY true_market_share_percent DESC;
Would really appreciate your assistance.