SQL Dialects and Built-in Functions Questions

Approach: for deterministic sampling you can either (A) use TABLESAMPLE ... REPEATABLE(seed) (fast, page-level), or (B) compute a deterministic pseudo-random score per row (hash) and ORDER BY that, then LIMIT 100 (exact count, fully deterministic, but expensive).

PostgreSQL: TABLESAMPLE (fast, approximate, repeatable)
SELECT *
FROM logs
TABLESAMPLE SYSTEM (1) REPEATABLE (123) -- percent of pages, repeatable with seed
LIMIT 100; -- TABLESAMPLE returns approximate number; LIMIT guarantees <=100

Deterministic exact sample (works regardless of DB random seed; scales with index if present on id):
SELECT *
FROM logs
ORDER BY ('x' || md5(id::text || '-seed-123'))::bit(64)::bigint
LIMIT 100;

Explanation and trade-offs:

• TABLESAMPLE SYSTEM/BERNOULLI
  • Pros: very fast for large tables because it reads a subset of pages; low IO.
  • Cons: returns approximate counts (need LIMIT to get exact 100), depends on physical row distribution (not uniform by row if rows per page vary), less control over uniformity within pages. REPEATABLE(seed) makes it reproducible for same table layout.
• ORDER BY random()/hash
  • ORDER BY random() is simple but nondeterministic unless you set session seed (setseed()) and forces a full-table sort — O(n log n) and heavy IO for large tables.
  • ORDER BY a hash (md5 or xxhash) yields deterministic, uniform-like ranking per row, exact LIMIT, and can be more efficient if you precompute/store the hash or use an index on the hash column.
    Recommendations:
• For ad-hoc fast sampling on massive tables use TABLESAMPLE ... REPEATABLE(seed) and accept approximation.
• For exact, reproducible sample of N rows use a hash-based ORDER BY or persist a random_score column updated once per seed.

Approach: convert UTC timestamps to America/Los_Angeles, truncate to date in that timezone, then count distinct users per local date for the last 30 days. I'll target PostgreSQL (ANSI-like) using AT TIME ZONE; for other dialects (BigQuery, Snowflake) functions differ but logic is same.

-- PostgreSQL
SELECT
  local_date AS date,
  COUNT(DISTINCT user_id) AS dau_count
FROM (
  SELECT
    user_id,
    -- convert UTC timestamp to America/Los_Angeles and take the date there
    (occurred_at AT TIME ZONE 'UTC' AT TIME ZONE 'America/Los_Angeles')::date AS local_date
  FROM events
  WHERE occurred_at >= (now() AT TIME ZONE 'UTC') - INTERVAL '30 days'
) t
GROUP BY local_date
ORDER BY local_date;

Key points:

• occurred_at is UTC; first AT TIME ZONE 'UTC' yields timestamp with time zone -> then AT TIME ZONE 'America/Los_Angeles' gives wall-clock time in that zone; cast to date to get local day.
• Filtering by occurred_at in UTC keeps predicate sargable; adjust interval boundary if inclusive/exclusive needed.
  Edge cases:
• Days with zero DAU will be missing — left join against a generated date series if you need zeros.
• Dialect differences: BigQuery uses TIMESTAMP_SECONDS/TIMESTAMP (with time zone conversion via TIMESTAMP(..., 'UTC') AT TIME ZONE), Snowflake uses CONVERT_TIMEZONE.

Approach: expand the JSON array into one row per product per session (explode), filter NULL/empty, then aggregate distinct session counts per product. I'll show both Postgres (jsonb) and BigQuery; both handle NULL/empty explicitly.

Postgres (jsonb):

-- Count sessions where a product appears at least once
SELECT p.product_id, COUNT(DISTINCT s.session_id) AS sessions_count
FROM sessions s
-- ignore NULL products
CROSS JOIN LATERAL (
  SELECT jsonb_array_elements_text(s.products) AS product_id
) p
WHERE s.products IS NOT NULL
  AND jsonb_typeof(s.products) = 'array'
  AND (s.products <> '[]'::jsonb)
GROUP BY p.product_id
ORDER BY sessions_count DESC;

BigQuery (standard SQL):

SELECT product_id, COUNT(DISTINCT session_id) AS sessions_count
FROM sessions,
UNNEST(
  CASE
    WHEN products IS NULL THEN [] -- treat NULL as empty
    WHEN JSON_TYPE(products) != 'ARRAY' THEN [] 
    ELSE JSON_EXTRACT_ARRAY(products)
  END
) AS prod
CROSS JOIN UNNEST([JSON_VALUE(prod)]) AS product_id
GROUP BY product_id
ORDER BY sessions_count DESC;

Key points / edge cases:

• Explicitly handle NULL and non-array JSON to avoid errors.
• Empty arrays produce zero rows; NULL produces zero rows via guard.
• Deduplicate per session if same product appears multiple times using COUNT(DISTINCT session_id).

Optimization suggestions:

• Normalize: store purchases in a junction table (session_products(session_id, product_id)) during ingestion; best for query performance and indexing.
• If keeping JSON: use Postgres jsonb with GIN index on products to accelerate containment queries (e.g., products @> '["123"]'), but GIN doesn't help well for exploding/aggregations.
• In BigQuery: store product_ids as native ARRAY<STRING> instead of JSON string to enable efficient UNNEST and columnar compression; cluster/partition the table (partition by date) and cluster by product_id to speed aggregations.
• Pre-aggregate daily/session-level materialized views if queries are frequent.

Trade-offs:

• Normalization increases write complexity but gives best query performance and smaller storage for analytics.
• JSON/ARRAY keeps schema-flexibility but costs more CPU to parse at query time; prefer native arrays over serialized JSON when possible.

T-SQL to determine server product/version/edition

-- Product/version string
SELECT @@VERSION AS version_string;

-- Individual properties
SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion,  -- e.g., 15.0.2000.5
       SERVERPROPERTY('ProductLevel') AS ProductLevel,      -- e.g., RTM/SP1
       SERVERPROPERTY('Edition') AS Edition,                -- e.g., Enterprise
       SERVERPROPERTY('EngineEdition') AS EngineEdition;    -- 1=Personal,2=Standard,3=Enterprise,4=SQL Azure

What each field means for operations & licensing

• ProductVersion/ProductLevel: exact build informs available features, bug fixes, and compatibility for connectors and tooling.
• Edition: indicates feature set (e.g., Enterprise has advanced compression, partitioning) and licensing implications for cost and feature availability.
• EngineEdition: numeric code useful for detecting cloud/managed instances vs on-prem; impacts supported features and administrative expectations.

As a Data Engineer, this tells me whether features like partition-level restore, columnstore, or snapshot isolation are available and informs deployment/compatibility decisions for ETL tools and performance tuning.

Approach: use PostgreSQL's POSIX regex match operators. Use . to match a literal dot and $ to anchor the end of the string. Use ~ for case-sensitive matching and ~* for case-insensitive matching.

Example SQL (case-sensitive):

-- returns emails that literally end with ".edu" or ".org" (case-sensitive)
SELECT email
FROM users
WHERE email ~ '\.(edu|org)$';

Example SQL (case-insensitive):

-- returns emails ending with .edu or .org regardless of case (e.g. .EDU, .Org)
SELECT email
FROM users
WHERE email ~* '\.(edu|org)$';

Key points / reasoning:

• . escapes the dot (otherwise . matches any character).
• (edu|org) is an alternation group for the two TLDs.
• $ ensures the TLD is at the end of the string (so "user@school.edu1" won't match).

Case-sensitive vs case-insensitive:

• ~ performs a case-sensitive POSIX regex match (so "user@X.edu" only matches if the case in pattern matches).
• ~* performs a case-insensitive match (ignores letter case).

Performance note:

• Regex filters generally can't use a plain b-tree index. For large tables, consider:
  • creating a trigram index (pg_trgm) for regex acceleration, or
  • storing/ indexing a lowercased email and querying with an expression index: CREATE INDEX ON users (lower(email)); then use WHERE lower(email) ~ '.(edu|org)$' for more efficient case-insensitive searches.