StudyFox
कीमतेंब्लॉगएक्सटेंशनमुफ़्त टूल
  • कीमतें
  • ब्लॉग
  • एक्सटेंशन
  • मुफ़्त टूल
  • शुरू करेंसाइन इन
StudyFox

अपने नोट्स, PDF और स्लाइड्स को फ़्लैशकार्ड, क्विज़, नोट्स और माइंड मैप में बदलें।

StudyFox खोलें

स्टडी टूल्स

  • होमवर्क हेल्पर
  • नोट मेकर
  • फ़्लैशकार्ड मेकर
  • क्विज़ मेकर
  • माइंड मैप मेकर

प्रोडक्ट

  • कीमतें
  • Chrome एक्सटेंशन
  • मुफ़्त एक्सटेंशन
  • स्टडी सेट देखें
  • ब्लॉग

सहायता

  • सहायता केंद्र
  • संपर्क करें
  • गोपनीयता नीति
  • सेवा की शर्तें

तुलना करें

  • Chegg का विकल्प
  • Course Hero का विकल्प
  • Coursology का विकल्प
  • Photomath का विकल्प
  • Gauthmath का विकल्प
  • PhotoSolve का विकल्प
  • CollegeTools का विकल्प
  • Transcript का विकल्प
  • SmartSolve का विकल्प
  • Homeworkify का विकल्प
  • AnswersAi का विकल्प
  • StudyX का विकल्प
  • Quizlet का विकल्प
  • Anki का विकल्प
  • Knowt का विकल्प
  • Brainscape का विकल्प
  • सभी तुलनाएँ

© 2026 StudyFox AI. सर्वाधिकार सुरक्षित।

  • X
  • Discord
  • Instagram
ExploreEngineering & TechData Science & AISQL Window Functions and Advanced Queries: Predict the Output

Quiz · Hard

SQL Window Functions and Advanced Queries: Predict the Output

Running totals and frames, LAG, LAST_VALUE, ranking with ties, NTILE, moving averages, integer division and gaps-and-islands, all with worked tables.

by Kill h.15 questionsCreated Nov 20, 2026, 08:26 PM
Data Science & AI· SQLTags#sql#window-functions#analytics

15 questions

  1. 1
    sales(day, rep, amount)
    2025-01-01 Ana 100 | 2025-01-02 Ana 150 | 2025-01-02 Ben 150
    2025-01-03 Ana 120 | 2025-01-03 Ben 90 | 2025-01-04 Ben 200 | 2025-01-05 Ana 60

    What value does run have in the row (2025-01-02, Ben)?

    SELECT day, rep, SUM(amount) OVER (ORDER BY day) AS run
    FROM sales ORDER BY day, rep
    • 400

    • B

      250

    • C

      150

    • D

      100

    With ORDER BY and no frame, the default is RANGE UNBOUNDED PRECEDING to CURRENT ROW, which includes all peers (rows with the same day). Both 2025-01-02 rows therefore show 100 + 150 + 150 = 400.

  2. 2
    sales(day, rep, amount)
    2025-01-01 Ana 100 | 2025-01-02 Ana 150 | 2025-01-02 Ben 150
    2025-01-03 Ana 120 | 2025-01-03 Ben 90 | 2025-01-04 Ben 200 | 2025-01-05 Ana 60

    What value does run have in the row (2025-01-03, Ben)?

    SELECT day, rep, SUM(amount) OVER (
      ORDER BY day, rep ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run
    FROM sales
    • A

      520

    • B

      400

    • 610

    • D

      710

    Ordered by day then rep, the rows before and including (01-03, Ben) are 100, 150, 150, 120 and 90, which add to 610. Ordering by a unique key makes the running total deterministic.

  3. 3
    sales(day, rep, amount)
    2025-01-01 Ana 100 | 2025-01-02 Ana 150 | 2025-01-02 Ben 150
    2025-01-03 Ana 120 | 2025-01-03 Ben 90 | 2025-01-04 Ben 200 | 2025-01-05 Ana 60

    What is prev for the day 2025-01-03?

    SELECT day, total, LAG(total) OVER (ORDER BY day) AS prev
    FROM (SELECT day, SUM(amount) AS total FROM sales GROUP BY day) d
    • A

      210

    • 300

    • C

      100

    • D

      NULL

    Daily totals are 100, 300, 210, 200, 60. LAG returns the previous row's total: 300 for 01-03. Only the first day gets NULL.

  4. 4
    sales(day, rep, amount)
    2025-01-01 Ana 100 | 2025-01-02 Ana 150 | 2025-01-02 Ben 150
    2025-01-03 Ana 120 | 2025-01-03 Ben 90 | 2025-01-04 Ben 200 | 2025-01-05 Ana 60

    What is lv in the row (2025-01-01, Ana)?

    SELECT day, rep, amount, LAST_VALUE(amount) OVER (
      PARTITION BY rep ORDER BY day) AS lv
    FROM sales
    • A

      60

    • B

      150

    • C

      NULL

    • 100

    The default frame ends at the current row, so LAST_VALUE is the current row's own amount. To get Ana's last amount (60) add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

  5. 5
    emp(id, name, manager_id, dept, salary)
    1 Rhea NULL Eng 150 | 2 Sam 1 Eng 120 | 3 Tia 1 Eng 120
    4 Uma 2 Eng 100 | 5 Vik 3 Ops 90 | 6 Wes 1 Ops 95

    What is dr for Uma (Eng, salary 100)?

    SELECT name, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dr FROM emp
    • A

      4

    • 3

    • C

      2

    • D

      1

    Eng salaries in descending order: 150 (1), 120 and 120 (both 2), 100 (3). DENSE_RANK has no gaps. RANK would give Uma 4.

  6. 6
    emp(id, name, manager_id, dept, salary)
    1 Rhea NULL Eng 150 | 2 Sam 1 Eng 120 | 3 Tia 1 Eng 120
    4 Uma 2 Eng 100 | 5 Vik 3 Ops 90 | 6 Wes 1 Ops 95

    How many Eng employees does WHERE rk <= 2 return when rk is RANK() by salary descending within the department? (ROW_NUMBER with the same filter would return 2.)

    • A

      2

    • B

      4

    • C

      1

    • 3

    Rhea has rank 1; Sam and Tia tie for rank 2. All three satisfy rk <= 2, while ROW_NUMBER would split the tie arbitrarily and return only two.

  7. 7
    emp(id, name, manager_id, dept, salary)
    1 Rhea NULL Eng 150 | 2 Sam 1 Eng 120 | 3 Tia 1 Eng 120
    4 Uma 2 Eng 100 | 5 Vik 3 Ops 90 | 6 Wes 1 Ops 95

    NTILE(4) is applied to these 6 rows ordered by salary. How many rows go into each of buckets 1, 2, 3 and 4?

    • 2, 2, 1, 1

    • B

      2, 2, 2, 0

    • C

      1, 1, 2, 2

    • D

      2, 1, 2, 1

    6 rows over 4 buckets: each bucket gets 1 row and the remaining 2 rows go to the first buckets, giving sizes 2, 2, 1, 1.

  8. 8
    sales(day, rep, amount)
    2025-01-01 Ana 100 | 2025-01-02 Ana 150 | 2025-01-02 Ben 150
    2025-01-03 Ana 120 | 2025-01-03 Ben 90 | 2025-01-04 Ben 200 | 2025-01-05 Ana 60

    What percent of the grand total (870) is Ben's 200 sale on 2025-01-04, computed as 100.0 * amount / SUM(amount) OVER ()?

    • A

      20.0%

    • B

      11.5%

    • 23.0%

    • D

      33.3%

    200 / 870 = 22.99%, rounded to 23.0. OVER () with nothing inside makes the whole table one window.

  9. 9
    sales(day, rep, amount)
    2025-01-01 Ana 100 | 2025-01-02 Ana 150 | 2025-01-02 Ben 150
    2025-01-03 Ana 120 | 2025-01-03 Ben 90 | 2025-01-04 Ben 200 | 2025-01-05 Ana 60

    Daily totals are 100, 300, 210, 200, 60. What is ma3 on 2025-01-03?

    SELECT day, AVG(total) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma3
    FROM (SELECT day, SUM(amount) AS total FROM sales GROUP BY day) d
    • 203.3

    • B

      300.0

    • C

      210.0

    • D

      236.7

    The frame holds the current row and the 2 before it: (100 + 300 + 210) / 3 = 203.3. On 01-04 it would be (300 + 210 + 200) / 3 = 236.7.

  10. 10

    What is wrong with this query?

    SELECT name FROM emp
    WHERE ROW_NUMBER() OVER (ORDER BY salary DESC) = 1;
    • A

      ROW_NUMBER needs a PARTITION BY clause.

    • B

      DESC is not allowed in a window ORDER BY.

    • Window functions cannot be used in WHERE; compute it in a subquery or CTE and filter outside.

    • D

      Nothing, it returns the top earner.

    Window functions are evaluated after WHERE, GROUP BY and HAVING. Wrap the window in a subquery and put WHERE rn = 1 in the outer query.

  11. 11

    In PostgreSQL, what does SELECT 7 / 2 return?

    • A

      3.5

    • 3 (integer division)

    • C

      4

    • D

      2

    Both operands are integers, so the result is an integer, truncated toward zero. Use 7 / 2.0 or 7::numeric / 2 for 3.5. MySQL's / would return 3.5 instead.

  12. 12

    What does this return?

    SELECT COALESCE(NULLIF(0, 0), -1)
    • A

      0

    • B

      NULL

    • C

      An error: division by zero

    • -1

    NULLIF(0, 0) returns NULL because the arguments are equal; COALESCE then returns the first non-NULL argument, -1.

  13. 13
    sales(day, rep, amount)
    2025-01-01 Ana 100 | 2025-01-02 Ana 150 | 2025-01-02 Ben 150
    2025-01-03 Ana 120 | 2025-01-03 Ben 90 | 2025-01-04 Ben 200 | 2025-01-05 Ana 60

    What does this conditional aggregation return?

    SELECT SUM(CASE WHEN day > '2025-01-03' THEN amount ELSE 0 END) FROM sales WHERE rep = 'Ben'
    • A

      440

    • 200

    • C

      240

    • D

      60

    Ben's sales after 2025-01-03: only the 200 on 01-04. 440 is Ben's total and 240 is his total up to 01-03.

  14. 14

    The activity days for one user are 1, 2, 3, 5, 6 (as day numbers). In the gaps-and-islands technique you subtract ROW_NUMBER() OVER (ORDER BY day) from each day. How many distinct values (islands) result?

    • A

      1

    • B

      5

    • C

      3

    • 2

    Day minus row number gives 0, 0, 0, 1, 1 for the days 1, 2, 3, 5, 6, so there are 2 islands: days 1-3 and days 5-6.

  15. 15

    You want the 3 most recent orders per customer. Which approach is correct?

    • ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) in a subquery, then WHERE rn <= 3

    • B

      ORDER BY order_date DESC LIMIT 3 GROUP BY customer_id

    • C

      SELECT TOP 3 customer_id, MAX(order_date) FROM orders GROUP BY customer_id

    • D

      WHERE COUNT(*) <= 3

    LIMIT applies to the whole result, not per group, and aggregates cannot be used in WHERE. A ranked window per customer followed by an outer filter is the standard pattern (LATERAL joins also work in PostgreSQL).