78 real SQL Analytics questions from the Data Engineering bank, as asked in Indian campus drives and tech interviews. Every question has a verified answer and an AI-tutor explanation on placd — free to start.
1. What does LAG do?
Junior
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.
B.Window function that distributes ordered rows into a specified number of roughly equal buckets, used for quartiles, deciles, and percentiles.
C.Window function that returns a value from a previous row within the partition at a given offset, useful for period-over-period comparisons.
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.
2. Which command will Window function that returns a value from a previous row within the partition at a given offset, useful for period-over-period comparisons.?
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.
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.
C.LAG — BigQuery function that merges HyperLogLog++ sketches to produce an approximate distinct count across pre-aggregated partitions.
D.LAG — The regular-expression-style clause inside MATCH_RECOGNIZE that names row-pattern variables and quantifiers to match ordered sequences of rows.
5. Which command will 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.?
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.
B.LEAD — Window frame mode that counts frame boundaries in terms of peer groups (distinct ORDER BY values) rather than individual rows.
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.
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.
8. Which command will Window function that distributes ordered rows into a specified number of roughly equal buckets, used for quartiles, deciles, and percentiles.?
A.NTILE — Clause (Snowflake, BigQuery, Databricks) that filters rows based on the result of a window function, avoiding a wrapping subquery.
B.NTILE — Aggregate that counts unique values; on big data often approximated with APPROX_COUNT_DISTINCT (HyperLogLog) for speed.
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.
D.NTILE — Window function that distributes ordered rows into a specified number of roughly equal buckets, used for quartiles, deciles, and percentiles.
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.
B.FIRST_VALUE — Window function returning the first value in an ordered window frame, often used to fetch the earliest event per partition.
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.
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.
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.
B.Aggregate that counts unique values; on big data often approximated with APPROX_COUNT_DISTINCT (HyperLogLog) for speed.
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.
D.Clause (Snowflake, BigQuery, Databricks) that filters rows based on the result of a window function, avoiding a wrapping subquery.
14. Which command will Window function returning the last value in an ordered window frame; needs an explicit frame (ROWS BETWEEN ... AND UNBOUNDED FOLLOWING) to behave as expected.?
A.LAST_VALUE — BigQuery function returning approximate percentile boundaries from a sketch, far cheaper than exact percentiles on large data.
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.
C.LAST_VALUE — GROUP BY extension producing subtotals for every possible combination of the grouped columns, a full cross-tabulation.
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.
17. Which command will Inverse-distribution function computing a continuous percentile by interpolating between values, returning a value that may not exist in the data.?
A.PERCENTILE_CONT — Window function that distributes ordered rows into a specified number of roughly equal buckets, used for quartiles, deciles, and percentiles.
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.
C.PERCENTILE_CONT — The regular-expression-style clause inside MATCH_RECOGNIZE that names row-pattern variables and quantifiers to match ordered sequences of rows.
D.PERCENTILE_CONT — Inverse-distribution function computing a continuous percentile by interpolating between values, returning a value that may not exist in the data.
A.Inverse-distribution function returning the discrete value at the requested percentile, always an actual value present in the dataset.
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.
C.Snowflake pattern combining a LATERAL join with the FLATTEN table function to explode semi-structured VARIANT arrays into one row per element.
D.BigQuery function returning approximate percentile boundaries from a sketch, far cheaper than exact percentiles on large data.
20. Which command will Inverse-distribution function returning the discrete value at the requested percentile, always an actual value present in the dataset.?
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.
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.
C.PERCENTILE_DISC — Window frame mode that counts frame boundaries in terms of peer groups (distinct ORDER BY values) rather than individual rows.
D.PERCENTILE_DISC — Inverse-distribution function returning the discrete value at the requested percentile, always an actual value present in the dataset.
A.Inverse-distribution function computing a continuous percentile by interpolating between values, returning a value that may not exist in the data.
B.Clause letting one query compute multiple groupings (subtotals) in a single pass without UNION ALL of separate aggregations.
C.Window function returning the first value in an ordered window frame, often used to fetch the earliest event per partition.
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.
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.
B.Window function that returns a value from a previous row within the partition at a given offset, useful for period-over-period comparisons.
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.
D.GROUP BY extension producing hierarchical subtotals and a grand total across a left-to-right ordered list of columns.
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.
B.Snowflake pattern combining a LATERAL join with the FLATTEN table function to explode semi-structured VARIANT arrays into one row per element.
C.GROUP BY extension producing subtotals for every possible combination of the grouped columns, a full cross-tabulation.
D.BigQuery function returning approximate percentile boundaries from a sketch, far cheaper than exact percentiles on large data.
A.CUBE — Aggregate that counts unique values; on big data often approximated with APPROX_COUNT_DISTINCT (HyperLogLog) for speed.
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.
C.CUBE — GROUP BY extension producing subtotals for every possible combination of the grouped columns, a full cross-tabulation.
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.
Showing 30 of 78 SQL Analytics questions — the full set, with answers, explanations and an AI tutor on every question, is inside.
Free to start
Answers, AI explanations, and a scored voice mock interview
Sign up free to check your answers with explanations, ask the AI tutor anything on any question, and take one full AI mock interview — scored like a real panel.