How can you combine FIRST_VALUE and LAST_VALUE to show the first and last login times per user from a logins table with columns user_id and login_time?
hard📝 Application Q9 of 15
PostgreSQL - Window Functions in PostgreSQL
How can you combine FIRST_VALUE and LAST_VALUE to show the first and last login times per user from a logins table with columns user_id and login_time?
ASELECT user_id, FIRST_VALUE(login_time) OVER (ORDER BY login_time), LAST_VALUE(login_time) OVER (ORDER BY login_time) FROM logins;
BSELECT user_id, MIN(login_time), MAX(login_time) FROM logins GROUP BY user_id;
CSELECT user_id, FIRST_VALUE(login_time), LAST_VALUE(login_time) FROM logins;
DSELECT user_id, FIRST_VALUE(login_time) OVER (PARTITION BY user_id ORDER BY login_time), LAST_VALUE(login_time) OVER (PARTITION BY user_id ORDER BY login_time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM logins;
Step-by-Step Solution
Solution:
Step 1: Partition by user_id and order by login_time
This ensures first and last login times are per user ordered by time.
Step 2: Use frame clause for LAST_VALUE
LAST_VALUE requires frame ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get last login time per user.
Step 3: Check options
SELECT user_id, FIRST_VALUE(login_time) OVER (PARTITION BY user_id ORDER BY login_time), LAST_VALUE(login_time) OVER (PARTITION BY user_id ORDER BY login_time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM logins; correctly uses window functions with partition, order, and frame. Others use aggregates or miss clauses.
Final Answer:
SELECT user_id, FIRST_VALUE(login_time) OVER (PARTITION BY user_id ORDER BY login_time), LAST_VALUE(login_time) OVER (PARTITION BY user_id ORDER BY login_time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM logins; -> Option D
Quick Check:
Combine FIRST_VALUE and LAST_VALUE with full frame [OK]
Quick Trick:LAST_VALUE needs full frame to get last row per partition [OK]
Common Mistakes:
Using aggregates instead of window functions
Omitting frame clause for LAST_VALUE
Not partitioning by user_id
Master "Window Functions in PostgreSQL" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
