This query compares the sessions of the last 7 days to the previous 7 days aggregated by day. This query demonstrates the use of a concept that can be used with any dimension and metric\s, comparing different values of the metric based on different values of the dimension.
SELECT TO_CHAR(datehour, 'Day') day_of_week, SUM( CASE WHEN datehour >= current_date - 7 AND datehour < current_date THEN sessions END ) last_7, SUM( CASE WHEN datehour >= current_date - 14 AND datehour < current_date - 7 THEN sessions END ) previous_7 FROM public."google-analytics" -- Table name might be different based on Schema and Destination settings in the data source WHERE datehour >= current_date - 14 AND datehour < current_date GROUP BY 1 ORDER BY MIN( CASE WHEN datehour BETWEEN current_date - 7 AND current_date - 1 THEN datehour END ) ASC
||Day of the week extracted from datehour|
||Values for last 7 days|
||Values for previous 7 days|