mirror of
https://github.com/robindhole/fundamentals.git
synced 2025-03-15 21:50:16 +00:00
2.1 KiB
2.1 KiB
Window functions worksheet
- Print the student names along with the number of students in their batch.
SELECT
id,
first_name,
last_name,
batch_id,
iq,
COUNT(*) OVER (PARTITION BY batch_id) AS batch_size
FROM
students;
- Print the student names along with the average IQ of their batch.
SELECT
id,
first_name,
last_name,
batch_id,
iq,
AVG(iq) OVER (PARTITION BY batch_id) AS batch_avg_iq
FROM
students;
- Print the student names along with the maximum IQ of their batch.
SELECT
id,
first_name,
last_name,
batch_id,
iq,
MAX(iq) OVER (PARTITION BY batch_id) AS batch_max_iq
FROM
students;
- Print the student names along with their rank based on IQ.
SELECT
id,
first_name,
last_name,
batch_id,
iq,
RANK() OVER (ORDER BY iq) AS iq_rank
FROM
students;
- Print the student names along with their rank in their batch based on IQ.
SELECT
id,
first_name,
last_name,
batch_id,
iq,
RANK() OVER (PARTITION BY batch_id ORDER BY iq) AS batch_iq_rank
FROM
students;
- Print the student names along with their rank in their batch based on IQ. Ties should not increase the rank.
SELECT
id,
first_name,
last_name,
batch_id,
iq,
DENSE_RANK() OVER (PARTITION BY batch_id ORDER BY iq) AS batch_iq_rank
FROM
students;
- Print the student names along with their rank in their batch based on IQ. Ties should not be counted and broken by ID.
SELECT
id,
first_name,
last_name,
batch_id,
iq,
ROW_NUMBER() OVER (PARTITION BY batch_id ORDER BY iq, id) AS batch_iq_rank
FROM
students;
- Find the rank of batches based on the start date of the batch.
SELECT
id,
name,
start_date,
RANK() OVER (ORDER BY start_date) AS batch_rank
FROM
batches;
- Sort the instructors by the first and last name and assign them a roll number.
SELECT
id,
first_name,
last_name,
ROW_NUMBER() OVER (ORDER BY first_name, last_name) AS roll_number
FROM
instructors;