RepetoHow do you write an unambiguous PostgreSQL date literal for 6 November 2026?
DATE '2026-11-06'.
SQL for data analysis
278 questions in English using PostgreSQL 18: joins, nulls, dates, window functions and analytical queries.
- 278 cards
- By Repeto
- Free
What the cards look like
For values 2, 2 and NULL, what do count(*) and count(x) return?
count(*) returns 3; count(x) returns 2.
What does jsonb_typeof('null'::jsonb) return?
The text 'null', not SQL NULL.
What is EXISTS (SELECT 1 WHERE FALSE)?
False, because the subquery returns no rows.
How can columns jan and feb on each row become month/value rows using a lateral VALUES expression?
CROSS JOIN LATERAL (VALUES ('Jan', jan), ('Feb', feb)) AS v(month, amount).
Why does sum(DISTINCT amount) not generally fix order amounts duplicated by a join?
It deduplicates equal amount values, so two different legitimate orders with the same amount are collapsed too.
All 278 cards
Every card in the deck, in study order.
Question Answer
How do you write an unambiguous PostgreSQL date literal for 6 November 2026? DATE '2026-11-06'.
For values 2, 2 and NULL, what do count(*) and count(x) return? count(*) returns 3; count(x) returns 2.
What does jsonb_typeof('null'::jsonb) return? The text 'null', not SQL NULL.
What is EXISTS (SELECT 1 WHERE FALSE)? False, because the subquery returns no rows.
How can columns jan and feb on each row become month/value rows using a lateral VALUES expression? CROSS JOIN LATERAL (VALUES ('Jan', jan), ('Feb', feb)) AS v(month, amount).
Why does sum(DISTINCT amount) not generally fix order amounts duplicated by a join? It deduplicates equal amount values, so two different legitimate orders with the same amount are collapsed too.
With rows ('A','web'), ('A','store'), ('B','web'), how many groups does GROUP BY region, channel produce? Three, one for each distinct region/channel combination.
For non-null updated_at and unique id, how can PostgreSQL select the latest event per user with deterministic ties using DISTINCT ON? SELECT DISTINCT ON (user_id) * FROM events ORDER BY user_id, updated_at DESC, id DESC.
Which delimiters distinguish an identifier named Order Total from a string value? Double quotes delimit the identifier: "Order Total". Single quotes delimit a string value.
Why should an analysis not assume text always sorts by raw character code? Text comparisons and sorting can depend on collation; specify a suitable collation when the ordering rule matters.
What snapshot property makes Repeatable Read useful for a multi-query analytical report? The statements share a stable transaction snapshot established by its first non-transaction-control statement, while seeing the transaction's own changes.
What does date_bin('15 minutes', TIMESTAMP '2026-01-01 10:17:00', TIMESTAMP '2026-01-01 00:00:00') return? 2026-01-01 10:15:00, the start of the aligned 15-minute bin.
What does SELECT 7 / 2 return when both literals are integers? 3. Integer division truncates toward zero.
What must PostgreSQL recognize about a query's WHERE condition to use a partial index? That the query condition implies the index predicate, so all needed rows are covered by that index.
What output does SELECT x * 2 AS y FROM (VALUES (3), (5)) AS v(x) ORDER BY x produce? Two rows with y values 6 and 10, in that order.
How do you keep a left row when its correlated LATERAL subquery returns no rows? Use LEFT JOIN LATERAL (...) alias ON TRUE.
In a LIKE pattern, how do % and _ differ? % matches any sequence of zero or more characters; _ matches exactly one character.
Does a window function see rows removed by the same query level's WHERE clause? No. Window processing happens after WHERE filtering and any grouping/HAVING at that level.
Why do fixed start/end instants and an explicit time zone improve reproducibility over a report defined only as 'today'? They fix the time window and calendar interpretation instead of letting execution time or session settings change the selected population.
Which expression converts the ASCII text 'PAID' to lowercase? lower('PAID'), producing 'paid'.
For descending scores 100, 90, 90, 80, what rank() values result? 1, 2, 2, 4; tied rows share a rank and leave a gap.
Why can adding a same-named column to two tables change the meaning of an existing NATURAL JOIN? NATURAL JOIN uses all shared column names as join keys, so the new column becomes another condition.
Which predicate keeps both false and null values of a Boolean column verified? verified IS NOT TRUE.
Why can count(*) = count(DISTINCT user_id) fail when user_id contains NULL even without repeated non-null ids? COUNT(*) includes the null row, whereas COUNT(DISTINCT user_id) excludes nulls.
Does TIMESTAMP '2026-01-01 12:00:00+02' become timestamptz because its text contains an offset? No. TIMESTAMP means without time zone, and the offset is ignored; use TIMESTAMPTZ for an offset-bearing instant.
What does DATE '2026-01-10' - DATE '2026-01-03' return? The integer 7, measured in days.
Why is NOT EXISTS with an explicit key equality usually clearer for an anti-join than NOT IN over a nullable key column? It tests the absence of matching rows directly; a null in the inner key column does not make every unmatched outer comparison unknown.
Orders and shipments identify an item by (order_id, line_no). Why can joining only on order_id create incorrect pairs? It can pair different lines of the same order; both key components are needed to match the intended item.
Why can a large OFFSET be expensive even when LIMIT is small? The server still has to compute the skipped rows before returning the requested page.
Does length('é') count characters or UTF-8 bytes for a text value? Characters, so it returns 1; octet_length measures bytes.
For ARRAY['red','blue'], what element does index 1 retrieve under PostgreSQL's default array bounds? 'red'; ordinary PostgreSQL array constructors use one-based bounds.
What does concat('A', NULL, 'B') return? 'AB'; concat ignores null arguments.
Does the presence of an index guarantee PostgreSQL will use it for a query? No. The planner can choose another access path when its estimated cost is lower.
What does 'A' || NULL::text return? SQL NULL; the concatenation operator propagates the null operand.
What does regexp_replace('a1b2', '[0-9]', '', 'g') return? 'ab'; the g flag replaces all matches.
What does PostgreSQL evaluate -7 / 2 to for integer operands? -3, because truncation is toward zero rather than toward negative infinity.
What does analytics.orders specify more explicitly than orders alone? It names the orders relation in the analytics schema instead of relying on the search path.
Does UNION align input columns by their names or their positions? By position, so each select list must put corresponding meanings in the same positions.
How do you name the output of price * quantity as revenue in a SELECT list? Write price * quantity AS revenue.
Events are sorted per user, and a new session begins after a gap strictly greater than 30 minutes. Should a gap of exactly 30 minutes start a new session? No. The stated rule requires gap > INTERVAL '30 minutes', not >=.
Why can WHERE o.status = 'paid' after a LEFT JOIN remove customers with no orders? Their right-side status is NULL, so the predicate is not true and WHERE removes them.
What does split_part('north|web|paid', '|', 2) return? 'web', the second field.
Which PostgreSQL function returns the current wall-clock time even as a statement runs? clock_timestamp().
In A UNION B INTERSECT C without parentheses, which set operation binds more tightly? INTERSECT, so the expression means A UNION (B INTERSECT C).
What type results from subtracting one timestamp from another? An interval representing their difference.
What kind of index can support predicates on lower(email) directly? An expression index on lower(email), rather than assuming an ordinary email index matches the transformed expression.
What does sum(amount) OVER () calculate on each row? The sum over all rows available to the window calculation, repeated on each row.
A query returns {1,2,3} and another returns {2,4}. What does the first EXCEPT the second return? The distinct values {1,3}; EXCEPT is directional.
How many groups does GROUP BY category create from category values 'A', NULL, NULL? Two: one for 'A' and one for the null keys.
For Boolean values TRUE, FALSE and NULL, what do bool_and and bool_or return? bool_and returns false; bool_or returns true. Null inputs are ignored.
What does sum(x) return when its input contains no rows? SQL NULL, not zero.
What does round(-2.5::numeric) return in PostgreSQL? -3. Numeric rounding breaks ties away from zero.
For values 10 and 20, what does percentile_cont(0.5) WITHIN GROUP (ORDER BY x) return? 15, interpolating between the two values.
For non-null created_at and unique non-null id, ordered ascending by (created_at, id), what predicate gets rows after the last timestamp $1 and id $2? WHERE (created_at, id) > ($1, $2), paired with ORDER BY created_at, id and a limit; supply the parameters through the client.
What value does a scalar subquery produce if it returns no rows? SQL NULL.
Given one non-null revenue row per region and a nonzero grand total, which window expression computes each region's revenue share? revenue::numeric / sum(revenue) OVER ().
When grouping sets produce a null region subtotal placeholder, what distinguishes it from an actual null region value? GROUPING(region) is 1 when region is omitted from that grouping set, and 0 when it participates.
Without an enforced foreign key, how can an analyst find non-null orders.customer_id values absent from customers.id? Filter non-null customer_id values and use NOT EXISTS with the matching customer id condition.
How do you write the SQL string literal containing O'Brien without a special escape-string prefix? Write 'O''Brien', doubling the embedded single quote.
What predicate selects numeric x from 10 inclusive to 20 exclusive? x >= 10 AND x < 20.
Show the other 218 cards
Why can a materialized view show older data than its underlying tables? It stores query results that remain as last populated or refreshed; base-table changes do not automatically refresh them.
Which grouping sets does ROLLUP(region, channel) generate? (region, channel), (region), and (), giving detail, region subtotals and the grand total.
Why should an ISO week number be paired with ISOYEAR rather than always with the calendar YEAR? Dates near New Year can belong to an ISO week in a different ISO year.
Why can joining facts to a dimension with duplicate join keys inflate a sum of fact amounts? Each fact can match multiple dimension rows, repeating its amount in the joined input.
What does CASE WHEN 8 > 5 THEN 'high' WHEN 8 > 2 THEN 'medium' ELSE 'low' END return? 'high'; CASE uses the first condition that is true.
Why can rounding each transaction before summing differ from rounding the sum once? Rounding discards information at different stages, so the two operations need not produce the same total.
How can an analysis produce a row for every day even when a fact table has no events on some days? Generate the day series, then left join facts aggregated to one row per day.
What query finds duplicate non-null user_id keys in events? SELECT user_id, count(*) FROM events WHERE user_id IS NOT NULL GROUP BY user_id HAVING count(*) > 1.
Does timestamptz preserve the original named time zone supplied with an input value? No. It represents an instant internally; output is converted to the session time zone, and the original zone name is not retained.
What PostgreSQL pattern selects customers c for whom no order has o.customer_id = c.id? Use WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id).
How do you keep only customer groups with at least three input rows? GROUP BY customer_id HAVING count(*) >= 3.
What weekday numbers does EXTRACT(ISODOW FROM d) use? Monday is 1 through Sunday as 7.
Why can SELECT * make a saved analytical output's columns change after a table gains a column? The star expands to the available columns. An explicit select list fixes which columns the query requests.
What does replace('a-b-c', '-', '/') return? 'a/b/c'; all matching substrings are replaced.
For x values 1, 1, 2, what does sum(x) OVER (ORDER BY x) return in that order? 2, 2, 4, because the default frame includes the full peer group.
Why would a newly formed cohort's next-month retention be incomplete before the next month ends? Not every cohort member has had the full defined observation window, so the measured numerator is not comparable to a completed window.
For descending scores 100, 90, 90, 80, what dense_rank() values result? 1, 2, 2, 3; tied groups do not leave gaps.
What is PostgreSQL's default transaction isolation level? Read Committed.
Does outer ORDER BY customer_id control the element order inside array_agg(event_id)? No. Specify ORDER BY inside array_agg to control its element order.
What does array_length(ARRAY[]::integer[], 1) return? SQL NULL, rather than zero, for the absent dimension.
Why is a large gap between estimated and actual row counts useful when investigating a slow plan? It indicates the planner's cardinality assumptions differ from the observed data, which can affect join and access choices.
What does GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW include? The current peer group and the immediately preceding peer group, rather than just one preceding row.
What does NOT NULL::boolean evaluate to? SQL NULL; negation does not turn unknown into true.
How do you filter for rn = 1 when rn is row_number() calculated in the SELECT list? Put the window calculation in a subquery or CTE, then filter rn in an outer query.
A monthly table contains January and March but no February. What prior row does LAG give March when ordered by month? January. LAG moves by rows, so it does not invent the missing February row.
What is FALSE AND NULL::boolean? False; the false operand is enough to make the conjunction false.
Why can adding INTERVAL '1 day' to timestamptz differ from adding INTERVAL '24 hours' in a daylight-saving zone? A calendar day can span a different elapsed number of hours across a daylight-saving transition.
If orders.customer_id references customers.id, does that guarantee every customer has an order? No. It constrains referencing order values, not whether every customer is referenced.
Is it correct that a PostgreSQL 18 multicolumn B-tree index can never help when its first column has no query condition? No. Later-column conditions can still be useful, and PostgreSQL 18 may use skip scan when cost-effective.
Can a query rely on rows being returned in insertion order when it has no ORDER BY? No. PostgreSQL does not guarantee an output order without ORDER BY.
How can you display zero for a group whose sum(amount) is NULL because every amount is NULL? Use COALESCE(sum(amount), 0), while deciding whether replacing unknown totals with zero suits the analysis.
Which PostgreSQL predicate treats two null values as equal for comparison? IS NOT DISTINCT FROM.
Do two SQL NULL keys match in an ordinary join condition a.key = b.key? No. Their equality is unknown, not true.
What makes a subquery correlated? It refers to columns from an outer query, so its meaning depends on the current outer row.
Which PostgreSQL operator checks whether a text contains a match for a case-sensitive POSIX regular expression? The ~ operator; unlike LIKE, an unanchored regular expression need not match the whole string.
What must be added to a window ORDER BY if row_number() must choose consistently among equal timestamps? A deterministic tie-breaker, such as a unique id.
A query returns {1,2,3} and another returns {2,3,4}. What does INTERSECT return without ALL? The distinct common values {2,3}.
Is every nonrecursive PostgreSQL CTE necessarily a separately materialized optimization barrier? No. A side-effect-free, nonrecursive CTE can be folded into its parent query; MATERIALIZED and NOT MATERIALIZED can influence eligible cases.
Under PostgreSQL's default UNIQUE behavior, can a nullable column contain more than one NULL? Yes. Nulls are treated as distinct by default; NULLS NOT DISTINCT changes that behavior.
Does UNION ALL guarantee that every row from its left query appears before every row from its right query? No. Use a final ORDER BY to request the required result order.
What does COALESCE(NULL, 0, 7) return? 0, the first non-null argument; zero is not treated as missing.
Why should the final query have ORDER BY even if an inner subquery was sorted? Later operations can reorder rows, and only the outer ordering requests the final output order.
Does a simple foreign key alone forbid NULL in its referencing column? No. Add NOT NULL when missing references must be forbidden.
What does PostgreSQL return for SELECT 4 + 6 AS total when no FROM clause is present? One row with total = 10.
Does CURRENT_TIMESTAMP advance during a long-running PostgreSQL transaction? No. It remains the transaction start timestamp.
Where does PostgreSQL place nulls by default in ORDER BY score DESC? Before non-null values, equivalent to NULLS FIRST.
For a timestamp window key ts, what frame includes rows from exactly six days before ts through its current peer group? RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW, with ORDER BY ts as the single ordering expression.
What does 2 IN (1, 2, NULL) evaluate to? True; an actual equal match is sufficient despite the null item.
Which predicate selects rows whose closed_at value is SQL NULL? closed_at IS NULL.
How can you obtain a numeric SQL value from a valid numeric score stored in jsonb payload? Use (payload ->> 'score')::numeric; invalid numeric text still raises a cast error.
What aggregate counts only rows whose status is 'paid' without filtering other aggregates in the same SELECT? count(*) FILTER (WHERE status = 'paid').
Which PostgreSQL pattern operator makes 'Report' match 'report' without manually applying lower? ILIKE, using case-insensitive matching according to the active locale.
What is TRUE OR NULL::boolean? True; the true operand is enough to make the disjunction true.
Which built-in number type is appropriate when decimal addition must be exact: numeric or double precision? Numeric. Double precision stores approximate floating-point values.
How can two window expressions reuse PARTITION BY region ORDER BY day without repeating that specification? Define WINDOW w AS (PARTITION BY region ORDER BY day), then use OVER w for both expressions.
After customers c LEFT JOIN orders o, which counts zero for a customer with no orders: count(*) or count(o.id), assuming o.id is never null in real orders? count(o.id). The null-extended row is counted by count(*) but not by count(o.id).
What does CASE WHEN FALSE THEN 1 END return? SQL NULL, because no condition is true and ELSE is omitted.
How can you make the expression 7 / 2 yield 3.5 in PostgreSQL? Cast an operand to numeric, for example 7::numeric / 2, or use a numeric literal such as 7.0 / 2.
Does count(*) FILTER (WHERE approved) count a row where approved is NULL? No. The FILTER condition must be true.
What timestamp predicate includes all of January 2026 without guessing its last fractional second? ts >= TIMESTAMP '2026-01-01' AND ts < TIMESTAMP '2026-02-01', when ts uses the same no-zone convention.
What does count(*) return when its input contains no rows? 0.
Why does 'abcde' LIKE 'bcd' return false? LIKE matches the whole string; use '%bcd%' to allow surrounding characters.
A customer_totals table has one row per customer, while payments has many rows per customer. How can you join payment totals without repeating the customer total once per payment? Aggregate payments to one row per customer before joining on customer_id.
Which frame includes the current row and up to two preceding rows? ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.
What does lag(amount) return for the first row of a partition when no default argument is supplied? SQL NULL, because no preceding row exists.
Left keys are 1, 2 and the right table contains only key 2. What happens to left key 1 in a LEFT JOIN on equality? It survives with null values for the right-side columns.
For a B-tree index on (region, sold_at), which predicate shape naturally constrains the leading key and then a time range? region = a_value together with a range on sold_at; whether the planner uses the index still depends on the query and data.
Why does ROWS BETWEEN 6 PRECEDING AND CURRENT ROW not necessarily mean the last seven calendar days? It counts rows, so missing dates or several rows per date change the time span covered.
What does a timestamp without time zone value represent without an external time-zone convention? A date and clock time without an attached zone or uniquely determined instant.
Does ORDER BY inside OVER guarantee the order of the final query output? No. It controls the window calculation; use a query-level ORDER BY for final output order.
Revenue rises from 80 to 100. What is percentage growth relative to the old value? 25%, calculated as (100 - 80) / 80 * 100 with numeric division.
Why is SELECT region, customer_name, sum(amount) FROM sales GROUP BY region invalid when customer_name has no recognized functional dependency on region? A group may contain several customer names, so PostgreSQL cannot choose a single ungrouped, non-aggregated value.
What does '{"plan":"pro","active":true}'::jsonb @> '{"plan":"pro"}'::jsonb return? True; the left document contains the specified key/value pair.
How can one row per customer show separate sums for 'web' and 'store' channels without a pivot extension? Group by customer_id and use separate sum(amount) FILTER (WHERE channel = 'web') and FILTER (WHERE channel = 'store') expressions.
What value results from 12.345::numeric(5,2)? 12.35, because the value is rounded to the declared scale.
Why can row_number() <= 3 return exactly three rows while rank() <= 3 returns more when boundary scores tie? ROW_NUMBER assigns distinct positions; RANK gives the same rank to peers, so all boundary ties can qualify.
How many rows result from a CROSS JOIN of a 3-row table and a 4-row table? 12, because every left row is paired with every right row.
How do you sort score from highest to lowest while keeping missing scores at the end? ORDER BY score DESC NULLS LAST.
How does JOIN ... USING (customer_id) differ from ON a.customer_id = b.customer_id in the joined table's output columns? USING emits one merged customer_id column; ON retains both input key columns.
What is the result of 10 + NULL::integer? SQL NULL; ordinary arithmetic propagates a null operand.
For distinct activity dates, why can activity_date - row_number() OVER (ORDER BY activity_date)::integer form a key for consecutive-date islands? Both the date and row number advance by one per day, so their difference stays constant within a consecutive run.
How do you concatenate non-null names alphabetically with commas in PostgreSQL? string_agg(name, ',' ORDER BY name).
Why does CASE x WHEN NULL THEN 'missing' ELSE 'present' END fail to identify a null x? The simple CASE uses equality comparisons, and equality with NULL is unknown. Use CASE WHEN x IS NULL THEN 'missing' ELSE 'present' END.
What does 3 NOT IN (1, 2, NULL) evaluate to? SQL NULL, so a WHERE clause using it would not keep the row.
Unlike count(x), does array_agg(x) include SQL NULL values among its elements? Yes. array_agg collects nulls as well as non-null input values.
Why does a unique surrogate row id not prove that a business key such as external_order_id is unique? Different row ids can still contain the same external_order_id; the business key needs its own validation or uniqueness constraint.
For 80 non-null amounts among 100 rows, what fraction of rows have an observed amount? 0.8, calculated as count(amount)::numeric / count(*), with a zero-denominator guard for an empty table.
Does SELECT city FROM customers remove repeated city values by default? No. SELECT preserves duplicate output rows unless DISTINCT or another operation removes them.
Which function expands a jsonb array into one JSONB value per output row? jsonb_array_elements.
After a first pass marks each session start with 1 and other rows with 0, how can SQL assign session numbers per user? Take a cumulative sum of the marker over each user's deterministic event order with a ROWS frame through the current row.
Left keys are 1, 2 and right keys are 2, 3. Which keys are represented by a FULL JOIN on equality? 1, 2 and 3, with the missing side null-extended for unmatched keys.
What expression makes revenue / units return NULL rather than fail when units is zero? Use revenue / NULLIF(units, 0), with a numeric operand if fractional division is required.
Why must a recursive hierarchy query account for cycles or otherwise guarantee termination? A cycle can keep producing recursive rows indefinitely; use an appropriate stop condition or cycle detection.
An orders table has one row per order, while order_items has one row per line item. Why can counting rows after joining them overcount orders? The join can produce several rows per order, so the result's grain is finer than one row per order.
Does PostgreSQL 18 implement IGNORE NULLS for lag and lead? No. They respect nulls, so null-valued rows still occupy positions.
What does substring('abcdef' FROM 2 FOR 3) return? 'bcd'; the first character position is 1.
How do payload -> 'score' and payload ->> 'score' differ for jsonb payload? -> returns JSONB; ->> extracts the value as text.
Regions have conversions/visits of 1/2 and 9/98. What overall conversion rate follows from the combined counts? 10/100 = 10%; use total conversions divided by total visits, not the unweighted mean of regional rates.
To retain every customer but match only paid orders, where should o.status = 'paid' go in a LEFT JOIN? In the ON condition, alongside the customer/order key equality.
What must match structurally between queries combined by UNION? They must return the same number of columns, with compatible types in corresponding positions.
A cohort has 100 users and 40 distinct cohort users return in the next month, generating 250 events. What is next-month user retention? 40%, using returning users divided by cohort users; event count is not the numerator.
Why is ORDER BY created_at alone insufficient for deterministic LIMIT 10 when timestamps repeat? Rows tied on created_at have no specified relative order. Add a unique tie-breaker such as id.
What is the role of the nonrecursive term in a WITH RECURSIVE query? It supplies the initial rows from which the recursive term starts.
Customers c and orders o both have an id column. How do you explicitly select the order's id? Use o.id in the SELECT list.
Left keys are 1, 2, 3 and right keys are 2, 3, 4, with each key appearing once. Which keys survive an inner equijoin? 2 and 3.
What does ts AT TIME ZONE 'America/New_York' return when ts is timestamptz? A timestamp without time zone showing that instant's local clock time in New York.
What does '[10,20]'::jsonb ->> 0 return? The text '10'; JSON array indexes start at zero.
How can one employees table represent both an employee and that employee's manager in a query? Join it to itself under two aliases, such as e JOIN employees m ON e.manager_id = m.id.
What is TRUE AND NULL::boolean? SQL NULL, because the unknown operand determines whether the conjunction is true.
What does EXTRACT(HOUR FROM TIMESTAMP '2026-07-19 15:42:00') return? 15.
Does PostgreSQL READ ONLY mode mean that every possible disk write or temporary-table change is forbidden? No. It restricts the documented data-changing commands, with temporary-table exceptions; it is a high-level transaction mode, not a guarantee of no physical writes.
Does EXPLAIN SELECT ... execute the query in the same way as EXPLAIN ANALYZE SELECT ...? No. Plain EXPLAIN shows the estimated plan; EXPLAIN ANALYZE executes the statement and reports observed execution statistics.
For a jsonb object payload, which expression checks for a top-level key named plan, regardless of its value? payload ? 'plan'.
For non-null scores, which ranking function can select the top three distinct scores while retaining every tie at those scores? Use dense_rank() OVER (ORDER BY score DESC) and keep ranks <= 3 in an outer query.
What does 7 = ANY (ARRAY[]::integer[]) evaluate to? False; there is no matching element.
What does (ARRAY[10,20])[3] return? SQL NULL, because index 3 is outside the bounds.
How does statement_timestamp() differ from transaction_timestamp()? It reports the start of the current statement, while transaction_timestamp() reports the transaction start.
What does cardinality(ARRAY[]::integer[]) return? 0, the number of elements in the empty array.
How many rows does generate_series(1, 5, 2) return, and what are their values? Three rows: 1, 3 and 5; the endpoint is included when reached by the step.
What does NULL::integer = NULL::integer evaluate to? SQL NULL, representing unknown, rather than true.
Why should (current - previous) / NULLIF(previous, 0) yield NULL when previous is zero rather than claim a finite growth rate? The relative change has a zero denominator, so the usual finite growth ratio is undefined.
For activity dates sorted per user, which window expression supplies the prior date so gaps can be measured? lag(activity_date) OVER (PARTITION BY user_id ORDER BY activity_date).
What aggregate counts SQL NULL amounts in a table? count(*) FILTER (WHERE amount IS NULL).
For rows (city, channel) = ('Oslo','web'), ('Oslo','store'), ('Oslo','web'), how many rows does SELECT DISTINCT city, channel return? Two: ('Oslo','web') and ('Oslo','store'); DISTINCT applies to the complete selected row.
Groups have means 10 and 20, based on 2 and 8 observations. Why is their unweighted mean, 15, not the combined observation mean? It gives the groups equal weight despite different sizes; the combined mean is (10 * 2 + 20 * 8) / 10 = 18.
What type does to_char(DATE '2026-07-04', 'YYYY-MM') return? Text, such as '2026-07'; it is a display label rather than a date value.
Does an ordinary PostgreSQL view store a frozen copy of its query results? No. It stores a query definition whose underlying query runs when the view is used.
Where does PostgreSQL place nulls by default in ORDER BY score ASC? After non-null values, equivalent to NULLS LAST.
For one row per event, which explicit frame gives a running sum through the current row rather than all peers? ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, with a deterministic window ORDER BY.
Why should a LEFT JOIN anti-join test use a right-side column known to be non-null for matched rows? A nullable marker could also be null in a real match, causing matched rows to be mistaken for unmatched ones.
For x values 10, NULL and 20, what is avg(x)? 15, because AVG excludes the null input.
For numeric(7,2), how many digits can appear to the left of the decimal point for ordinary finite values? Five; the total precision is seven digits and the scale reserves two fractional digits.
Can date_bin use an interval stride containing months? No. Its positive stride must not contain months or larger units.
What command starts a PostgreSQL transaction with both Repeatable Read isolation and read-only mode? BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY.
How is active OR premium AND paid grouped by PostgreSQL's operator precedence? As active OR (premium AND paid). Use parentheses when a different grouping is intended.
What does 5 IS DISTINCT FROM NULL evaluate to? True; exactly one operand is null.
With rows numbered 1 through 10 in that order, which row numbers does OFFSET 3 LIMIT 2 return? 4 and 5; OFFSET skips three before LIMIT takes two.
What group levels are requested by GROUP BY GROUPING SETS ((region), ())? One group per region plus one grand-total group.
Which two column properties does a PostgreSQL primary key enforce? Every key column is non-null, and the complete key value is unique.
Does 10 BETWEEN 10 AND 20 evaluate to true? Yes. BETWEEN includes both endpoints.
Why is INTERVAL '1 month' not interchangeable with INTERVAL '30 days' for date arithmetic? Calendar months have different lengths, so the resulting dates can differ.
Why does replacing an unknown transaction amount with zero change the meaning of an average? It adds a known zero to the calculation instead of excluding an unknown value, potentially lowering the result.
What does ts AT TIME ZONE 'UTC' return when ts is timestamp without time zone? A timestamptz obtained by interpreting that local clock time as UTC.
If an EXPLAIN ANALYZE node reports actual rows=5 loops=10, approximately how many rows did it emit across those executions? 50. Per-loop row counts are reported as averages, so multiply by the loop count.
Does lag(amount, 1, 0) replace a previous row's null amount with zero? No. The default is used only when the requested prior row is absent; a present null value remains null.
What does concat_ws('-', 'A', NULL, 'B') return? 'A-B'; null value arguments are skipped.
Are PostgreSQL EXPLAIN cost estimates measured directly in milliseconds? No. They are planner cost units; actual timing from EXPLAIN ANALYZE is reported separately.
What does PARTITION BY customer_id do in a window specification? It creates separate row partitions per customer, so the window calculation restarts independently for each customer.
Can ORDER BY revenue use an output alias defined as price * quantity AS revenue? Yes. PostgreSQL allows a standalone output alias in ORDER BY.
Can an analysis rely on JSONB preserving the input order of object keys? No. JSONB does not preserve object-key order or insignificant whitespace.
Which PostgreSQL aggregate uses the sample standard-deviation definition rather than the population definition? stddev_samp; stddev_pop uses the population definition.
For an events table with non-null user_id and event_date, what aggregate finds each user's first observed activity date? SELECT user_id, min(event_date) AS first_date FROM events GROUP BY user_id.
Does EXISTS (SELECT NULL) evaluate to true or false? True. The subquery returns a row; the selected value does not need to be non-null.
What does btrim(' east ') return with its default second argument? 'east', with spaces removed from both ends.
What directions does ORDER BY region, revenue DESC apply to its two columns? Region ascending and revenue descending; DESC applies only to revenue.
Does ntile(4) create equal-width numeric ranges of the ordered value? No. It divides ordered rows into four buckets as evenly as possible by row count.
What expression converts an empty or space-only text value s to SQL NULL after trimming default spaces? NULLIF(btrim(s), '').
For values 10 and 20, what does percentile_disc(0.5) WITHIN GROUP (ORDER BY x) return? 10, an observed value at the requested cumulative position.
Why does count(CASE WHEN paid THEN 1 ELSE 0 END) count unpaid rows too? Both 1 and 0 are non-null. Omit ELSE so unmatched rows yield NULL, or use FILTER.
A left table has keys 1 and 2; a right table has only key 2. How many rows remain after their LEFT JOIN followed by WHERE right_table.key IS NULL? One: the unmatched left key 1.
Does 'null'::jsonb IS NULL evaluate to true? No. JSON null is a JSON value, distinct from SQL NULL.
What makes two rows peers in a window calculation? They are not distinct under every expression in the window's ORDER BY; two null sort keys can therefore be peers.
An events table has duplicate event_key values, non-null updated_at timestamps and unique ids. How can you retain the latest row per key, breaking timestamp ties by id? Compute row_number() OVER (PARTITION BY event_key ORDER BY updated_at DESC, id DESC), then keep row number 1 in an outer query.
What does date_trunc('month', TIMESTAMP '2026-07-19 15:42:00') return? 2026-07-01 00:00:00, the start of that month.
Can WHERE revenue > 100 use revenue defined as price * quantity AS revenue in the same SELECT list? No. Repeat the expression or put the SELECT in a subquery and filter its revenue column outside.
What does DISTINCT ON (customer_id) keep from each customer group? The first row under the query's ordering; without a suitable ORDER BY, the chosen row is unpredictable.
What does '{"a":1}'::jsonb -> 'missing' return? SQL NULL, because the requested key does not exist.
For descending scores 9, 7, 7, 3, what percent_rank() does score 7 receive? 1/3, because its rank is 2 and the partition has 4 rows: (2 - 1) / (4 - 1).
How does avg(amount) OVER (PARTITION BY customer_id) differ in output grain from GROUP BY customer_id with avg(amount)? The window expression retains each input row and adds its customer's average; GROUP BY collapses each customer group to one row.
Does casting 'unknown' to integer produce SQL NULL in PostgreSQL? No. The cast raises an invalid-input error; validate or clean the value before casting.
Why is sum(sum(amount)) OVER () valid in a query grouped by region? The inner SUM produces each region's total; the outer window SUM operates across those grouped rows.
Why can a CTE make a query that aggregates daily revenue and then compares days easier to express? It names the daily aggregate result so the outer query can operate at that new row grain.
What does rows in an ordinary EXPLAIN node describe? The estimated number of rows that node emits, not necessarily all rows it scans or examines.
Why should counts at the intended business grain be checked before and after adding a join to a report? A join may duplicate or remove business entities, changing the population or totals being reported.
Can two SELECT statements in one PostgreSQL Read Committed transaction see different committed data? Yes. Each statement gets a new snapshot, so another transaction's intervening commit can become visible.
What does DATE '2026-01-31' + INTERVAL '1 month' produce? 2026-02-28 00:00:00, adjusted to the final valid day of February.
How can you calculate elapsed hours between two timestamptz values finish and start? EXTRACT(EPOCH FROM (finish - start)) / 3600.
Does a high corr(y, x) value by itself show that changing x causes y to change? No. It describes association in the paired data, not an established causal effect.
A product sold 2 units at price 10 and 8 units at price 20. What weighted average price should SQL compute? 18, using sum(price * units) / sum(units), with numeric division.
Which clause chooses output expressions, while WHERE decides which input rows survive? SELECT chooses the output expressions; WHERE filters input rows.
How can you select each customer who has at least one order without returning one row per matching order? Use WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) on the customer query.
For corr(y, x), is a row with a non-null y but null x included in the paired observations? No. Rows are used only when both inputs are non-null.
Why is SELECT DISTINCT * insufficient when duplicate business keys have different update timestamps? The complete rows differ, so DISTINCT keeps them both; deduplication needs the business key and a rule for choosing a row.
Query A returns 1, 1 and query B returns 1, 2. How many rows do UNION and UNION ALL produce? UNION produces 2 distinct rows; UNION ALL produces all 4 rows.
With a window ORDER BY and no explicit frame, does a running aggregate stop at just the physical current row? No. The default frame extends from the partition start through the current row's last peer.
For values 2, 2, 5 and NULL, what does count(DISTINCT x) return? 2, counting the distinct non-null values 2 and 5.
How can last_value(x) inspect the final row of the whole partition while retaining a window ORDER BY? Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, with a suitable deterministic ordering.
What does 17 % 5 return for integer operands? 2, the remainder after division.
What must the leftmost ORDER BY expressions match in a PostgreSQL DISTINCT ON query? The DISTINCT ON expressions; further ordering expressions choose the preferred row within each group.
How can an expanded set-returning function output include a position counter starting at 1? Add WITH ORDINALITY in FROM and give the added ordinal column a name.
Why can HAVING sum(amount) > 100 filter group totals while WHERE sum(amount) > 100 is invalid at that query level? WHERE filters rows before grouping; HAVING filters groups after aggregation.
What does '{"a":1,"a":2}'::jsonb keep for key a? The last value, 2; JSONB does not retain duplicate object keys.
For ordinary timestamps, on which weekday does date_trunc('week', ts) start the week? Monday at 00:00:00.
What is the scope of a name defined by WITH totals AS (...) in a statement? It is available within that statement; it does not create a persistent table.
Scores are 100, 90, 90 and 80. How many rows does ORDER BY score DESC FETCH FIRST 2 ROWS WITH TIES return? Three, because both rows tied at the second-row score are included.
Why does summing daily distinct-user counts generally overstate distinct users across the entire period? The same user can occur on several days and is counted again in each daily total.
What does 5 = ANY (SELECT 1 WHERE FALSE) return? False, because no comparison is true.
For ascending values 1, 2, 2, 4, what cume_dist() do the rows with value 2 receive? 0.75, because three of the four rows precede them or are their peers.
Why is grouping only by EXTRACT(MONTH FROM occurred_at) risky for a dataset spanning several years? It combines the same month number across years; include a year or use an appropriate month-start key.
Why can ts::date group a timestamptz differently after the session time zone changes? The instant is converted to the session's local date; choose an explicit analysis zone before extracting the date.
Which function accesses the next row's value under a window ordering: lag or lead? LEAD; LAG accesses a preceding row.
How does PostgreSQL treat the unquoted identifier CustomerID? It folds it to lowercase, customerid.
What does NULLIF(4, 4) return? SQL NULL, because its two arguments compare equal.
Does CHECK (amount > 0) alone reject a NULL amount? No. A CHECK passes when its result is true or null; add NOT NULL to forbid missing values.
For a single join key, the left table has 2 rows and the right table has 3 rows. How many matching rows does an equality join produce for that key? 6, one for each matching pair.
What condition can deliberately match null keys as well as equal non-null keys in PostgreSQL? a.key IS NOT DISTINCT FROM b.key.
Which aggregate sums amount only for paid rows while allowing a separate total over all rows? sum(amount) FILTER (WHERE status = 'paid').
What does PostgreSQL return for GREATEST(4, NULL, 9)? 9. PostgreSQL ignores null arguments here and returns NULL only if all arguments are null.
What happens if a scalar subquery returns more than one row? PostgreSQL raises an error; a scalar subquery must supply at most one row and one column.
For one row for every consecutive month, how can LAG support month-to-month revenue growth? Use lag(revenue) OVER (ORDER BY month) as the prior value, then calculate the relative change in an outer query with a zero-denominator guard.
What PostgreSQL cast expression converts the text value '42' to an integer? '42'::integer; CAST('42' AS integer) is the standard syntax alternative.
How do trunc(12.79::numeric, 1) and round(12.79::numeric, 1) differ? Truncation returns 12.7; rounding returns 12.8.
What does SELECT unnest(ARRAY[5,7,7]) return? Three rows, containing 5, 7 and 7; unnest does not remove duplicates.
Why does last_value(x) OVER (ORDER BY ts) often return the current peer group's value rather than the partition's final value? LAST_VALUE uses the window frame, whose default endpoint is the current row's last peer.
What does LATERAL allow a FROM subquery to reference? Columns from preceding FROM items on its left.
If a WHERE predicate evaluates to SQL NULL for a row, does that row survive? No. WHERE keeps only rows for which the predicate is true.
What does 7 = ANY (ARRAY[5,7,9]) evaluate to? True, because at least one element equals 7.
What does 5 > ALL (SELECT 1 WHERE FALSE) return? True; there is no comparison that violates the condition.
How you study it in Repeto
- Install the deck free. It lands in your library on the web, iPhone, iPad and Mac.
- Answer from memory. See the front, recall the answer, then flip and rate how it went.
- Review at the right time. FSRS spaced repetition brings each card back just before you would forget it, so a few minutes a day is enough.
Sources and license
Original Repeto-authored study cards and diagrams, based on the documented public educational sources; not endorsed by their publishers.