SQL Dialects and Built-in Functions Questions

Approach: use a time-aware window frame to compute, for each user and date, the 7-day rolling average of amount including the current day and the previous 6 days.

BigQuery / PostgreSQL (supports INTERVAL in RANGE):

SELECT
  user_id,
  date,
  AVG(amount) OVER (
    PARTITION BY user_id
    ORDER BY date
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS avg_7d
FROM transactions;

Notes: ORDER BY date must be a DATE/TIMESTAMP; RANGE with INTERVAL creates a sliding frame of timestamps/dates.

SQL Server problem: SQL Server's RANGE frame does not accept an INTERVAL-like offset on datetime types. Alternatives:

1. Simple and clear — correlated APPLY (or correlated subquery):

SELECT t.user_id,
       t.date,
       x.avg_7d
FROM (
  SELECT DISTINCT user_id, CAST(date AS date) AS date
  FROM transactions
) t
OUTER APPLY (
  SELECT AVG(amount) AS avg_7d
  FROM transactions tx
  WHERE tx.user_id = t.user_id
    AND CAST(tx.date AS date) BETWEEN DATEADD(day, -6, t.date) AND t.date
) x
ORDER BY t.user_id, t.date;

Pros: straightforward, easy to read, uses date arithmetic (DATEADD). Works well with proper indexes on (user_id, date).

2. More performant for large data — pre-aggregate per day then use cumulative sums and join to get range subtraction:

WITH daily AS (
  SELECT user_id, CAST(date AS date) AS date, SUM(amount) AS day_amount
  FROM transactions
  GROUP BY user_id, CAST(date AS date)
),
cum AS (
  SELECT
    d.*,
    SUM(day_amount) OVER (PARTITION BY user_id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_sum
  FROM daily d
)
SELECT
  c.date,
  c.user_id,
  (c.running_sum
   - ISNULL(c7.running_sum, 0)
   ) / 7.0 AS avg_7d
FROM cum c
LEFT JOIN cum c7
  ON c.user_id = c7.user_id
  AND c7.date = DATEADD(day, -7, c.date)  -- subtract running_sum up to day-7
ORDER BY c.user_id, c.date;

Explanation: daily aggregates reduce row count. running_sum gives total up to current day; subtract running_sum up to date-7 (if exists) to get sum over last 7 days, then divide by 7 (or use COUNT of days present if you want variable-length windows).

Choose APPLY for simplicity and smaller datasets; use the cumulative-sum pattern for large-scale data where window functions and pre-aggregation improve performance. Ensure proper indexing on (user_id, date) and consider handling missing dates if you want exactly 7-day denominators vs. available-day averages.

To get the first day of the month for order_date.

PostgreSQL:

SELECT DATE_TRUNC('month', order_date)::date AS month_start
FROM orders;

BigQuery (Standard SQL):

SELECT DATE_TRUNC(order_date, MONTH) AS month_start
FROM `project.dataset.orders`;

Snowflake:

SELECT DATE_TRUNC('MONTH', order_date)::date AS month_start
FROM orders;

SQL Server:

SELECT DATEADD(month, DATEDIFF(month, 0, order_date), 0) AS month_start
FROM orders;

Why DATE_TRUNC exists (and SQL Server workaround):

• DATE_TRUNC is a concise, readable function that normalizes a timestamp/date to a specified precision (month, day, hour, etc.). It’s native in PostgreSQL, BigQuery, Snowflake and many analytical engines because it’s convenient for time-bucketing in analytics.
• SQL Server historically used date arithmetic primitives (DATEADD, DATEDIFF) instead of a single truncation function; the DATEDIFF(month, 0, date) yields the number of months since the SQL Server epoch, and DATEADD re-applies that count to epoch 0 to produce the month-start. This is an idiomatic workaround to achieve truncation behavior. Newer SQL Server versions also include functions like DATETRUNC (SQL Server 2022+) which mirror DATE_TRUNC.

Situation: During fall DST rollback, local clock times repeat (e.g., 2023-11-05 01:30 in America/New_York happens twice). If treated only as naive local timestamps, reports can double-count or misorder events.

Detection:

• In ETL, detect ambiguous local times by attempting to convert local timestamp + timezone to UTC using a timezone library that exposes ambiguity (pytz/zoneinfo raise or provide fold/is_dst metadata).
• Flag rows where the conversion is ambiguous (fold flag in PEP 495 / zoneinfo).

Storage & data model changes (recommended):

• Always store a canonical UTC timestamp: event_ts_utc TIMESTAMP WITH TIME ZONE.
• Also persist original local values for audit: event_ts_local TIMESTAMP WITHOUT TIME ZONE, tz_name VARCHAR, tz_offset_seconds INT, and fold BOOLEAN (or is_ambiguous BOOLEAN).
• If events are user/actions that have natural ordering, include a monotonically increasing event_id or sequence_within_local INT to preserve ordering when local wall-clock ties exist.

Example columns:

• event_id (PK)
• event_ts_utc TIMESTAMPTZ
• event_ts_local TIMESTAMP
• tz_name TEXT
• tz_offset_seconds INT
• fold BOOLEAN
• seq_within_local INT

SQL example to convert and detect ambiguity (Postgres + zoneinfo):

-- assume local_ts and tz provided
SELECT
  local_ts,
  tz_name,
  -- convert to timestamptz using offset-aware construct
  (local_ts AT TIME ZONE tz_name) AT TIME ZONE 'UTC' as event_ts_utc
FROM events_stage;

(Use application-layer library to detect fold/ambiguity and set fold boolean; in Python zoneinfo, check datetime.fold.)

Disambiguation & reporting rules:

• For deduplication: dedupe on a unique business key (event_id or user_action_id) plus event_ts_utc. If no unique id, dedupe using (event_ts_utc, tz_name, seq_within_local) — prefer UTC as canonical.
• For ordering: sort by event_ts_utc, then by seq_within_local or event_id to break ties.
• For time-bucketed reports (hourly/day): decide whether buckets are local or UTC. If reporting in local time, map event_ts_utc -> local using tz_name and use fold to decide which repeated hour it belongs to; include both offset_seconds and fold in the mapping.
• For analytics dashboards: expose both local and UTC times and document how ambiguous times are handled. Provide filters to aggregate by UTC or by local-date with explicit DST-handling.

ETL best practices:

• Resolve ambiguity at ingestion: require client to send offset or explicit UTC conversion; if not possible, compute both possibilities, flag and request enrichment or use deterministic rule (prefer earlier/later fold) documented in data contract.
• Monitor: generate alerts when ambiguous timestamps are present and count occurrences.

Trade-offs:

• Storing extra fields increases storage but is cheap vs. analytical correctness.
• Making clients send UTC reduces ambiguity but requires engineering changes.

This approach prevents double-counting (use UTC for uniqueness/dedupe) and preserves business semantics (keep local values, tz, fold, and sequence for audits and correct local reporting).

Short answer: use timestamp with time zone (timestamptz) for event times. timestamptz stores an absolute instant (internally as UTC) and preserves the point in time across time zones; timestamp without time zone stores a “naive” local date/time with no zone context.

Why for BI:

• Events are often generated in multiple time zones or later viewed by users in different zones. timestamptz ensures consistent, unambiguous ordering and aggregation (e.g., daily counts) because it represents a single instant.
• Store in UTC (Postgres does this for timestamptz) and convert to user/local display at presentation layer.

Examples and common pitfalls:

• Ingesting logs with local timestamps into timestamp without time zone will lose source-zone info; same clock time from two zones becomes indistinguishable.
• Relying on client session timezone: timestamptz displays using the session timezone, so dashboards must explicitly convert rather than rely on DB default.
• Daylight Saving Time: naive timestamps can produce ambiguous or missing times around DST transitions.
• Comparing values: timestamp without time zone comparisons assume same timezone—dangerous across sources.

Helpful SQL patterns:

-- store: parse an ISO string with offset into timestamptz
INSERT INTO events (occurred_at) VALUES ('2025-11-22T09:30:00-05:00'::timestamptz);

-- display in a specific zone
SELECT occurred_at AT TIME ZONE 'America/Los_Angeles' FROM events;

Best practices:

• Use timestamptz, normalize to UTC on ingest, keep original source tz if needed, index the column, and convert to user timezone at reporting/visualization layer.

To return event_id plus extracted fields including ISO week, you can use EXTRACT or DATE_PART (they're synonyms) and DATE_TRUNC for bucketed timestamps. Example:

SELECT
  event_id,
  (event_ts AT TIME ZONE 'UTC')::date AS event_date,          -- date only (normalized to UTC if needed)
  EXTRACT(year FROM event_ts)    AS year,
  EXTRACT(month FROM event_ts)   AS month,
  EXTRACT(day FROM event_ts)     AS day,
  EXTRACT(hour FROM event_ts)    AS hour,
  EXTRACT(week FROM event_ts)    AS iso_week,                 -- ISO week number (1-53)
  DATE_TRUNC('day', event_ts)    AS day_bucket,
  DATE_TRUNC('week', event_ts)   AS week_bucket,              -- week starting Monday (ISO week start)
  DATE_TRUNC('month', event_ts)  AS month_bucket
FROM events;

Key differences and BI guidance:

• EXTRACT(field FROM timestamp) and DATE_PART(field, timestamp) return numeric components (year, month, day, hour, week). They are interchangeable; use them when you need scalar parts for grouping, filtering, or labels (e.g., group by EXTRACT(year FROM event_ts), EXTRACT(week FROM ...)).
• DATE_TRUNC(unit, timestamp) returns a timestamp rounded down to the unit (e.g., start of day/week/month). Use DATE_TRUNC when you want to bucket events into time intervals for aggregations (GROUP BY DATE_TRUNC('week', event_ts)) or to join by period boundaries.
• ISO week nuance: EXTRACT(week FROM ...) follows ISO week numbering; for week start alignment also use DATE_TRUNC('week', ...) which uses ISO (week starting Monday) in Postgres. For business reporting, ensure timezone handling (convert event_ts to the business timezone before casting to date or truncating).
  Examples:
• Count per ISO week: SELECT DATE_TRUNC('week', event_ts AT TIME ZONE 'America/Los_Angeles') AS week_start, COUNT(*) FROM events GROUP BY 1 ORDER BY 1;
• Pivot by year/month: GROUP BY EXTRACT(year FROM ...), EXTRACT(month FROM ...).