SQL Analytics interview questions

1. A.Function returning 1 or 0 to indicate whether a column was aggregated away in a ROLLUP or CUBE row, used to label subtotal rows.
2. B.Window function that distributes ordered rows into a specified number of roughly equal buckets, used for quartiles, deciles, and percentiles.
3. C.Window function that returns a value from a previous row within the partition at a given offset, useful for period-over-period comparisons.
4. D.Statement combining INSERT, UPDATE, and DELETE in one pass by matching a target table against a source on a join condition, the basis of upserts.

Answer + AI explanation with Pro

1. A.LAST_VALUE
2. B.LAG
3. C.COUNT DISTINCT
4. D.ROLLUP

Answer + AI explanation with Pro

1. A.LAG — Window function returning the last value in an ordered window frame; needs an explicit frame (ROWS BETWEEN ... AND UNBOUNDED FOLLOWING) to behave as expected.
2. B.LAG — Window function that returns a value from a previous row within the partition at a given offset, useful for period-over-period comparisons.
3. C.LAG — BigQuery function that merges HyperLogLog++ sketches to produce an approximate distinct count across pre-aggregated partitions.
4. D.LAG — The regular-expression-style clause inside MATCH_RECOGNIZE that names row-pattern variables and quantifiers to match ordered sequences of rows.

Answer + AI explanation with Pro

1. A.Statement combining INSERT, UPDATE, and DELETE in one pass by matching a target table against a source on a join condition, the basis of upserts.
2. B.MERGE clause that acts on target rows absent from the source, commonly used to delete or soft-delete rows no longer present upstream.
3. C.Window function that returns a value from a following row within the partition at a given offset, useful for looking ahead in an ordered series.
4. D.Clause letting one query compute multiple groupings (subtotals) in a single pass without UNION ALL of separate aggregations.

Answer + AI explanation with Pro

1. A.APPROX_QUANTILES
2. B.WHEN NOT MATCHED BY SOURCE
3. C.LEAD
4. D.GROUPS BETWEEN

Answer + AI explanation with Pro

1. A.LEAD — A window-function option (e.g. LAST_VALUE ... IGNORE NULLS) that skips null values, commonly used to carry forward the last known non-null value.
2. B.LEAD — Window frame mode that counts frame boundaries in terms of peer groups (distinct ORDER BY values) rather than individual rows.
3. C.LEAD — Window function that returns a value from a following row within the partition at a given offset, useful for looking ahead in an ordered series.
4. D.LEAD — Window-function modifier (for LAG, LEAD, FIRST_VALUE) that skips null values when locating the offset row, useful for carrying forward last-known values.

Answer + AI explanation with Pro

1. A.Window frame clause that defines the frame by logical value range of the ORDER BY column, grouping peer rows with the same value together.
2. B.Inverse-distribution function computing a continuous percentile by interpolating between values, returning a value that may not exist in the data.
3. C.Window function that distributes ordered rows into a specified number of roughly equal buckets, used for quartiles, deciles, and percentiles.
4. D.Window frame clause that defines the rows in a window by physical row position relative to the current row, e.g. 2 PRECEDING to CURRENT ROW.

Answer + AI explanation with Pro

1. A.MERGE
2. B.NTILE
3. C.LAG
4. D.CUBE

Answer + AI explanation with Pro

1. A.NTILE — Clause (Snowflake, BigQuery, Databricks) that filters rows based on the result of a window function, avoiding a wrapping subquery.
2. B.NTILE — Aggregate that counts unique values; on big data often approximated with APPROX_COUNT_DISTINCT (HyperLogLog) for speed.
3. C.NTILE — Window frame clause that defines the rows in a window by physical row position relative to the current row, e.g. 2 PRECEDING to CURRENT ROW.
4. D.NTILE — Window function that distributes ordered rows into a specified number of roughly equal buckets, used for quartiles, deciles, and percentiles.

Answer + AI explanation with Pro

1. A.Inverse-distribution function returning the discrete value at the requested percentile, always an actual value present in the dataset.
2. B.Window frame clause that defines the frame by logical value range of the ORDER BY column, grouping peer rows with the same value together.
3. C.Window frame clause that defines the rows in a window by physical row position relative to the current row, e.g. 2 PRECEDING to CURRENT ROW.
4. D.Window function returning the first value in an ordered window frame, often used to fetch the earliest event per partition.

Answer + AI explanation with Pro

1. A.RANGE BETWEEN
2. B.FIRST_VALUE
3. C.IGNORE NULLS
4. D.PERCENTILE_DISC

Answer + AI explanation with Pro

1. A.FIRST_VALUE — Window function that returns a value from a previous row within the partition at a given offset, useful for period-over-period comparisons.
2. B.FIRST_VALUE — Window function returning the first value in an ordered window frame, often used to fetch the earliest event per partition.
3. C.FIRST_VALUE — Window function that returns a value from a following row within the partition at a given offset, useful for looking ahead in an ordered series.
4. D.FIRST_VALUE — Window frame clause that defines the frame by logical value range of the ORDER BY column, grouping peer rows with the same value together.

Answer + AI explanation with Pro

1. A.Window-function modifier (for LAG, LEAD, FIRST_VALUE) that skips null values when locating the offset row, useful for carrying forward last-known values.
2. B.Aggregate that counts unique values; on big data often approximated with APPROX_COUNT_DISTINCT (HyperLogLog) for speed.
3. C.Window function returning the last value in an ordered window frame; needs an explicit frame (ROWS BETWEEN ... AND UNBOUNDED FOLLOWING) to behave as expected.
4. D.Clause (Snowflake, BigQuery, Databricks) that filters rows based on the result of a window function, avoiding a wrapping subquery.

Answer + AI explanation with Pro

1. A.PERCENTILE_DISC
2. B.LATERAL FLATTEN
3. C.MERGE
4. D.LAST_VALUE

Answer + AI explanation with Pro

1. A.LAST_VALUE — BigQuery function returning approximate percentile boundaries from a sketch, far cheaper than exact percentiles on large data.
2. B.LAST_VALUE — Window function returning the last value in an ordered window frame; needs an explicit frame (ROWS BETWEEN ... AND UNBOUNDED FOLLOWING) to behave as expected.
3. C.LAST_VALUE — GROUP BY extension producing subtotals for every possible combination of the grouped columns, a full cross-tabulation.
4. D.LAST_VALUE — A window-function option (e.g. LAST_VALUE ... IGNORE NULLS) that skips null values, commonly used to carry forward the last known non-null value.

Answer + AI explanation with Pro

1. A.Window-function modifier (for LAG, LEAD, FIRST_VALUE) that skips null values when locating the offset row, useful for carrying forward last-known values.
2. B.Window frame clause that defines the frame by logical value range of the ORDER BY column, grouping peer rows with the same value together.
3. C.Snowflake pattern combining a LATERAL join with the FLATTEN table function to explode semi-structured VARIANT arrays into one row per element.
4. D.Inverse-distribution function computing a continuous percentile by interpolating between values, returning a value that may not exist in the data.

Answer + AI explanation with Pro

1. A.GROUPING SETS
2. B.PERCENTILE_CONT
3. C.FIRST_VALUE
4. D.RANGE BETWEEN

Answer + AI explanation with Pro

1. A.PERCENTILE_CONT — Window function that distributes ordered rows into a specified number of roughly equal buckets, used for quartiles, deciles, and percentiles.
2. B.PERCENTILE_CONT — Window function returning the last value in an ordered window frame; needs an explicit frame (ROWS BETWEEN ... AND UNBOUNDED FOLLOWING) to behave as expected.
3. C.PERCENTILE_CONT — The regular-expression-style clause inside MATCH_RECOGNIZE that names row-pattern variables and quantifiers to match ordered sequences of rows.
4. D.PERCENTILE_CONT — Inverse-distribution function computing a continuous percentile by interpolating between values, returning a value that may not exist in the data.

Answer + AI explanation with Pro

1. A.Inverse-distribution function returning the discrete value at the requested percentile, always an actual value present in the dataset.
2. B.Window function returning the last value in an ordered window frame; needs an explicit frame (ROWS BETWEEN ... AND UNBOUNDED FOLLOWING) to behave as expected.
3. C.Snowflake pattern combining a LATERAL join with the FLATTEN table function to explode semi-structured VARIANT arrays into one row per element.
4. D.BigQuery function returning approximate percentile boundaries from a sketch, far cheaper than exact percentiles on large data.

Answer + AI explanation with Pro

1. A.PERCENTILE_DISC
2. B.HLL_COUNT.MERGE
3. C.GROUPING
4. D.LAST_VALUE

Answer + AI explanation with Pro

1. A.PERCENTILE_DISC — Statement combining INSERT, UPDATE, and DELETE in one pass by matching a target table against a source on a join condition, the basis of upserts.
2. B.PERCENTILE_DISC — Window function returning the last value in an ordered window frame; needs an explicit frame (ROWS BETWEEN ... AND UNBOUNDED FOLLOWING) to behave as expected.
3. C.PERCENTILE_DISC — Window frame mode that counts frame boundaries in terms of peer groups (distinct ORDER BY values) rather than individual rows.
4. D.PERCENTILE_DISC — Inverse-distribution function returning the discrete value at the requested percentile, always an actual value present in the dataset.

Answer + AI explanation with Pro

1. A.Inverse-distribution function computing a continuous percentile by interpolating between values, returning a value that may not exist in the data.
2. B.Clause letting one query compute multiple groupings (subtotals) in a single pass without UNION ALL of separate aggregations.
3. C.Window function returning the first value in an ordered window frame, often used to fetch the earliest event per partition.
4. D.Window function returning the last value in an ordered window frame; needs an explicit frame (ROWS BETWEEN ... AND UNBOUNDED FOLLOWING) to behave as expected.

Answer + AI explanation with Pro

1. A.APPROX_QUANTILES
2. B.CUBE
3. C.GROUPING SETS
4. D.LAG

Answer + AI explanation with Pro

1. A.GROUPING SETS — Clause (Snowflake, BigQuery, Databricks) that filters rows based on the result of a window function, avoiding a wrapping subquery.
2. B.GROUPING SETS — Aggregate that counts unique values; on big data often approximated with APPROX_COUNT_DISTINCT (HyperLogLog) for speed.
3. C.GROUPING SETS — Clause letting one query compute multiple groupings (subtotals) in a single pass without UNION ALL of separate aggregations.
4. D.GROUPING SETS — Function returning 1 or 0 to indicate whether a column was aggregated away in a ROLLUP or CUBE row, used to label subtotal rows.

Answer + AI explanation with Pro

1. A.Window function returning the last value in an ordered window frame; needs an explicit frame (ROWS BETWEEN ... AND UNBOUNDED FOLLOWING) to behave as expected.
2. B.Window function that returns a value from a previous row within the partition at a given offset, useful for period-over-period comparisons.
3. C.A window-function option (e.g. LAST_VALUE ... IGNORE NULLS) that skips null values, commonly used to carry forward the last known non-null value.
4. D.GROUP BY extension producing hierarchical subtotals and a grand total across a left-to-right ordered list of columns.

Answer + AI explanation with Pro

1. A.ROLLUP
2. B.ROWS BETWEEN
3. C.PERCENTILE_DISC
4. D.GROUPING

Answer + AI explanation with Pro

1. A.ROLLUP — Window-function modifier (for LAG, LEAD, FIRST_VALUE) that skips null values when locating the offset row, useful for carrying forward last-known values.
2. B.ROLLUP — Window function returning the first value in an ordered window frame, often used to fetch the earliest event per partition.
3. C.ROLLUP — GROUP BY extension producing hierarchical subtotals and a grand total across a left-to-right ordered list of columns.
4. D.ROLLUP — BigQuery function that merges HyperLogLog++ sketches to produce an approximate distinct count across pre-aggregated partitions.

Answer + AI explanation with Pro

1. A.Window function returning the last value in an ordered window frame; needs an explicit frame (ROWS BETWEEN ... AND UNBOUNDED FOLLOWING) to behave as expected.
2. B.Snowflake pattern combining a LATERAL join with the FLATTEN table function to explode semi-structured VARIANT arrays into one row per element.
3. C.GROUP BY extension producing subtotals for every possible combination of the grouped columns, a full cross-tabulation.
4. D.BigQuery function returning approximate percentile boundaries from a sketch, far cheaper than exact percentiles on large data.

Answer + AI explanation with Pro

1. A.APPROX_COUNT_DISTINCT
2. B.ROLLUP
3. C.COUNT DISTINCT
4. D.CUBE

Answer + AI explanation with Pro

1. A.CUBE — Aggregate that counts unique values; on big data often approximated with APPROX_COUNT_DISTINCT (HyperLogLog) for speed.
2. B.CUBE — Function returning 1 or 0 to indicate whether a column was aggregated away in a ROLLUP or CUBE row, used to label subtotal rows.
3. C.CUBE — GROUP BY extension producing subtotals for every possible combination of the grouped columns, a full cross-tabulation.
4. D.CUBE — Window function that returns a value from a following row within the partition at a given offset, useful for looking ahead in an ordered series.

Answer + AI explanation with Pro