如何在PostgreSQL中过滤掉周内数据?

Question

我有一个查询，我正在利用它进行仪表板可视化。查询的唯一问题是它包含当前周的数据 - 这是有问题的，因为该周尚未完全烘焙。理想情况下，我想添加一个过滤器，该过滤器提取过去 52 周的数据，但排除日期大于最近报告周(周日至周六(的数据。 这是我目前拥有的：

SELECT (DATE_TRUNC('week',cj.created_at:: timestamptz) + '5 days':: 
interval)::date
,CASE WHEN o.vertical IS NULL OR o.vertical NOT IN 
('Auto','Franchise') THEN 'SMB'
ELSE o.vertical END as vertical
,COUNT(DISTINCT cj.native_candidate_job_id) as applicant_traffic
FROM dim_candidate_jobs cj
JOIN dim_organizations o ON cj.native_organization_id = 
o.native_organization_id
WHERE o.demo = false
AND NOT(o.name ~~* any (array['%test%','%api%']))
AND cj.created_at:: date > current_date - interval '52 weeks'
AND cj.created_at:: date < (DATE_TRUNC('week',current_date:: 
timestamptz) + '1 day':: interval)::date
AND o.current = 'Y'
AND cj.current = 'Y'
GROUP BY 1,2;;

Answer 1

and cj.created_at >= date_trunc('week', current_date) - interval '52 weeks'
and cj.created_at < date_trunc('week', current_date)