BI & Analytics Quiz

Business intelligence and analytics: reporting, dashboards, and turning raw data into decisions.

This category currently has 100 questions in the SERVBG quiz bank. Below are a few sample questions: the full interactive quiz shuffles through the whole set with instant scoring.

Sample questions

Which window function clause restricts the frame to rows between the start of the partition and the current row, inclusive?

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

A query uses LAG(revenue, 1) OVER (PARTITION BY region ORDER BY month). What is the value returned for the first row of each partition?

0
The global average of revenue
The value of the last row in the partition
NULL
An error is raised

To perform gap-fill on a sparse time-series table, which SQL technique generates a complete date spine before a LEFT JOIN?

A recursive CTE that doubles the date range on each iteration via CROSS JOIN
WINDOW FILL FORWARD applied directly to the sparse table without a spine
PIVOT on the date column to transpose sparse values into columns
CROSS APPLY with DATEADD to expand only existing rows
A CTE using GENERATE_SERIES (PostgreSQL) or equivalent date-spine expression to produce every date in the range

In sessionisation logic, a new session is defined when the gap between consecutive events exceeds 30 minutes. Which window function approach correctly flags session boundaries?

Use DENSE_RANK() OVER (PARTITION BY user ORDER BY event_time) to number each event as a separate session
Use LEAD to get the next event timestamp and flag rows where the next gap exceeds 30 minutes, then apply ROW_NUMBER
Use NTILE(30) OVER (PARTITION BY user ORDER BY event_time) to split events into 30-minute buckets
Use FIRST_VALUE(event_time) OVER (PARTITION BY user) and subtract from every row to detect 30-minute gaps
Use LAG to get the previous event timestamp, then flag a new session where the difference exceeds 30 minutes, and use SUM of flags OVER (PARTITION BY user ORDER BY event_time) to assign session IDs

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) differs from PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) because:

PERCENTILE_CONT interpolates between adjacent values to return the exact median, while PERCENTILE_DISC returns the first actual value that meets or exceeds the percentile threshold
Both functions return identical results for even-numbered datasets with no duplicates
PERCENTILE_DISC interpolates between adjacent values; PERCENTILE_CONT always returns an existing row value
PERCENTILE_CONT only operates on integer columns; PERCENTILE_DISC works on any orderable type
PERCENTILE_DISC returns the median of the top half; PERCENTILE_CONT returns the median of all values

Start the full quiz ›