PostgreSQL GROUP RANK Quiz: Test Your Skills

Progress

True / False

The RANK() function in PostgreSQL can be used to assign a unique rank number to each row within a partition of a result set.

True False

The RANK() function assigns a rank to each row within the partition of the result set, with gaps in ranking where there are ties.

True / False

The GROUP BY clause in PostgreSQL is used to rank rows.

True False

The GROUP BY clause is used to group rows that have the same values in specified columns into aggregate data, not to rank rows.

True / False

In PostgreSQL, the DENSE_RANK() function produces the same result as the RANK() function if there are no duplicate values.

True False

If there are no duplicate values, both DENSE_RANK() and RANK() will produce the same ranking result.

True / False

The RANK() function in PostgreSQL will always increment the rank by 1 regardless of duplicate values.

True False

The RANK() function skips ranks when there are duplicate values, causing gaps in the ranking sequence.

True / False

The PARTITION BY clause is necessary when using the RANK() function in PostgreSQL to rank rows within groups.

True False

While PARTITION BY is often used to rank rows within groups, it is not mandatory. Without PARTITION BY, the function will rank the entire result set.

True / False

The ROW_NUMBER() function in PostgreSQL provides the same ranking as the RANK() function.

True False

The ROW_NUMBER() function assigns a unique number to each row in the result set, without gaps, unlike the RANK() function which can have gaps when there are ties.

True / False

In PostgreSQL, the RANK() function can be used in conjunction with ORDER BY to rank rows based on a specific column.

True False

The RANK() function is typically used with the ORDER BY clause to determine the rank of rows based on the values of one or more columns.

True / False

The CUME_DIST() function in PostgreSQL returns the rank of the current row as a percentage of the total rows in the partition.

True False

The CUME_DIST() function calculates the cumulative distribution of a value in a set of values, returning the rank of the current row as a percentage.

True / False

Using the NTILE() function in PostgreSQL, you can divide rows into a specified number of approximately equal groups and assign a group rank to each row.

True False

The NTILE() function divides an ordered result set into a specified number of groups and assigns a group number to each row.

Q10

True / False

The RANK() function in PostgreSQL can be used directly within the WHERE clause to filter results based on rank.

True False

The RANK() function cannot be used directly in the WHERE clause because RANK() is a window function and is computed after the WHERE clause is processed. To filter based on rank, you need to use a subquery or a common table expression (CTE).

Q11

Single Choice

In PostgreSQL, which function is commonly used to assign a rank to rows within a partition of a result set?

DENSE_RANK() ROW_NUMBER() RANK()NTILE()

The RANK() function in PostgreSQL assigns a rank to each row within a partition of a result set, with gaps in the ranking when there are ties.

Q12

Single Choice

What is the difference between the RANK() and DENSE_RANK() functions in PostgreSQL?

RANK() skips numbers after ties, while DENSE_RANK() does not. DENSE_RANK() skips numbers after ties, while RANK() does not. Both functions work the same wayRANK() assigns unique numbers to rows, while DENSE_RANK() does not.

The RANK() function skips numbers after ties in PostgreSQL, whereas DENSE_RANK() assigns consecutive numbers, even after ties.

Q13

Single Choice

Which SQL clause is necessary when using RANK() to partition rows in PostgreSQL?

ORDER BY GROUP BY PARTITION BYHAVING

The PARTITION BY clause is used to divide the result set into partitions, and the RANK() function assigns a rank to each row within each partition.

Q14

Single Choice

How would you rank employees by salary within each department using PostgreSQL?

SQL Code

SELECT
 employee_id,
 department_id,
 salary,
 RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;
What does the RANK() function do in this query?

It assigns a rank based on the salary within the entire table. It assigns a rank within each department, with ties receiving the same rank. It assigns a unique rank to each employee regardless of department.It ranks employees by salary in ascending order.

The RANK() function in this query ranks employees by salary within each department, with ties receiving the same rank.

Q15

Single Choice

Given the following PostgreSQL query, what would be the rank of an employee with a salary of 50000 if there are two other employees with the same salary in the same department?

SQL Code

SELECT
 employee_id,
 department_id,
 salary,
 RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

The rank would be 1. The rank would be 2. The rank would be 3.The rank would be shared as 2 with others.

In PostgreSQL, if there are ties, the RANK() function assigns the same rank to those rows and skips the subsequent rank number.

Q16

Single Choice

How can you modify the PostgreSQL query to ensure no ranks are skipped, even if there are ties?

SQL Code

SELECT
 employee_id,
 department_id,
 salary,
 _______ OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

Replace the blank with RANK() Replace the blank with DENSE_RANK() Replace the blank with ROW_NUMBER()Replace the blank with NTILE()

Using DENSE_RANK() instead of RANK() ensures that no ranks are skipped in PostgreSQL, even if there are ties.

Q17

Single Choice

Consider the following PostgreSQL query:

SQL Code

SELECT
 product_id,
 category_id,
 price,
 RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS rank
FROM products;
If there are two products with the same highest price in the same category, what will be the rank of the next product in that category?

1 2 34

The RANK() function will assign the same rank (1) to the two highest-priced products, and the next product will be ranked as 3.

Q18

Single Choice

In PostgreSQL, which of the following queries will rank items in descending order of quantity sold, with the highest quantity ranked as 1, without skipping ranks if there are ties?

SQL Code

SELECT
 item_id,
 category_id,
 quantity_sold,
 _______ OVER (PARTITION BY category_id ORDER BY quantity_sold DESC) AS rank
FROM sales;

RANK() DENSE_RANK() ROW_NUMBER()NTILE()

DENSE_RANK() ranks items without skipping ranks, even if there are ties in PostgreSQL.

Q19

Single Choice

How can you include only the top 3 ranked items in each category using the following PostgreSQL query?

SQL Code

SELECT
 item_id,
 category_id,
 price,
 RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS rank
FROM items
_______;

Add WHERE rank <= 3 Add HAVING rank <= 3 Add WHERE price > 3Add GROUP BY rank

The WHERE clause can be used to filter the result set in PostgreSQL to include only the top 3 ranked items in each category.

Q20

Single Choice

Given the following PostgreSQL table sales with columns salesperson_id, region_id, and total_sales, how can you rank salespersons by their total sales within each region and display only those with a rank of 1 or 2?

SQL Code

SELECT
 salesperson_id,
 region_id,
 total_sales,
 RANK() OVER (PARTITION BY region_id ORDER BY total_sales DESC) AS rank
FROM sales
_______;

Add HAVING rank <= 2 Add WHERE rank <= 2 Add GROUP BY rankAdd FILTER BY rank <= 2

The WHERE clause can be used in PostgreSQL to filter the results and display only salespersons with a rank of 1 or 2 within each region.

Q21

Multiple Choice

Which of the following SQL queries correctly ranks employees within their department based on their salary?

SELECT employee_id, department_id, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank FROM employees; SELECT employee_id, department_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank FROM employees; SELECT employee_id, department_id, salary, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary ASC) AS rank FROM employees;SELECT employee_id, department_id, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees;

The ROW_NUMBER() function with PARTITION BY is commonly used to rank items within a group, such as employees within a department.

Q22

Multiple Choice

Which SQL queries correctly use GROUP RANK functions to list products by category in order of sales?

SELECT product_id, category_id, sales, RANK() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rank FROM products; SELECT product_id, category_id, sales, DENSE_RANK() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rank FROM products; SELECT product_id, category_id, sales, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rank FROM products;SELECT product_id, category_id, sales, RANK() OVER (ORDER BY sales DESC) AS rank FROM products;

RANK(), DENSE_RANK(), and ROW_NUMBER() are all functions that can be used to rank items within a group, each serving slightly different purposes.

Q23

Multiple Choice

Which of the following SQL queries correctly rank departments by their total budget?

SELECT department_id, budget, RANK() OVER (ORDER BY budget DESC) AS rank FROM departments; SELECT department_id, budget, DENSE_RANK() OVER (ORDER BY budget DESC) AS rank FROM departments; SELECT department_id, budget, ROW_NUMBER() OVER (ORDER BY budget DESC) AS rank FROM departments;SELECT department_id, budget, RANK() OVER (PARTITION BY department_id ORDER BY budget DESC) AS rank FROM departments;

RANK() and DENSE_RANK() can be used to rank groups, such as departments, based on aggregate values like total budget.

Q24

Multiple Choice

Which SQL queries correctly implement group ranking for employees based on their hire date within each department?

SELECT employee_id, department_id, hire_date, RANK() OVER (PARTITION BY department_id ORDER BY hire_date) AS rank FROM employees; SELECT employee_id, department_id, hire_date, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY hire_date ASC) AS rank FROM employees; SELECT employee_id, department_id, hire_date, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY hire_date ASC) AS rank FROM employees;SELECT employee_id, department_id, hire_date, RANK() OVER (ORDER BY hire_date ASC) AS rank FROM employees;

ROW_NUMBER(), RANK(), and DENSE_RANK() are functions that can be used to rank employees within each department based on a specific order, such as hire date.

Q25

Multiple Choice

Which SQL queries correctly rank customers by their total purchase amount within each region?

SELECT customer_id, region, total_purchase, RANK() OVER (PARTITION BY region ORDER BY total_purchase DESC) AS rank FROM customers; SELECT customer_id, region, total_purchase, DENSE_RANK() OVER (PARTITION BY region ORDER BY total_purchase DESC) AS rank FROM customers; SELECT customer_id, region, total_purchase, ROW_NUMBER() OVER (PARTITION BY region ORDER BY total_purchase DESC) AS rank FROM customers;SELECT customer_id, region, total_purchase, RANK() OVER (ORDER BY total_purchase DESC) AS rank FROM customers;

The PARTITION BY clause is used with ranking functions to group data before applying the ranking logic, allowing you to rank customers within each region.

Q26

Multiple Choice

Which SQL queries correctly implement ranking to find the top 3 products by sales within each category?

SELECT product_id, category_id, sales, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rank FROM products WHERE rank <= 3; SELECT product_id, category_id, sales, RANK() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rank FROM products WHERE rank <= 3; SELECT product_id, category_id, sales, DENSE_RANK() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rank FROM products WHERE rank <= 3;SELECT product_id, category_id, sales, ROW_NUMBER() OVER (ORDER BY sales DESC) AS rank FROM products WHERE rank <= 3;

Ranking functions like ROW_NUMBER() can be used in combination with a WHERE clause to limit the results to the top N records within each group.

Q27

Multiple Choice

Which SQL queries correctly use the GROUP RANK functions to rank students by their GPA within each class?

SELECT student_id, class_id, gpa, RANK() OVER (PARTITION BY class_id ORDER BY gpa DESC) AS rank FROM students; SELECT student_id, class_id, gpa, DENSE_RANK() OVER (PARTITION BY class_id ORDER BY gpa DESC) AS rank FROM students; SELECT student_id, class_id, gpa, ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY gpa DESC) AS rank FROM students;SELECT student_id, class_id, gpa, RANK() OVER (ORDER BY gpa DESC) AS rank FROM students;

Using PARTITION BY with ranking functions allows you to create ranks for students within each class based on their GPA.

Q28

Multiple Choice

Which SQL queries correctly rank branches by their total revenue within each state?

SELECT branch_id, state, revenue, RANK() OVER (PARTITION BY state ORDER BY revenue DESC) AS rank FROM branches; SELECT branch_id, state, revenue, DENSE_RANK() OVER (PARTITION BY state ORDER BY revenue DESC) AS rank FROM branches; SELECT branch_id, state, revenue, ROW_NUMBER() OVER (PARTITION BY state ORDER BY revenue DESC) AS rank FROM branches;SELECT branch_id, state, revenue, RANK() OVER (ORDER BY revenue DESC) AS rank FROM branches;

The PARTITION BY clause can be used to rank branches within a specific state, allowing you to compare revenue across branches within that group.

Q29

Multiple Choice

Which SQL queries correctly implement group ranking for sales representatives based on their quarterly sales?

SELECT rep_id, region, quarterly_sales, RANK() OVER (PARTITION BY region ORDER BY quarterly_sales DESC) AS rank FROM sales_reps; SELECT rep_id, region, quarterly_sales, DENSE_RANK() OVER (PARTITION BY region ORDER BY quarterly_sales DESC) AS rank FROM sales_reps; SELECT rep_id, region, quarterly_sales, ROW_NUMBER() OVER (PARTITION BY region ORDER BY quarterly_sales DESC) AS rank FROM sales_reps;SELECT rep_id, region, quarterly_sales, RANK() OVER (ORDER BY quarterly_sales DESC) AS rank FROM sales_reps;

RANK(), DENSE_RANK(), and ROW_NUMBER() functions can be applied within a group to rank records such as sales representatives within each region.

Q30

Multiple Choice

Which SQL queries correctly rank projects by their completion percentage within each department?

SELECT project_id, department_id, completion_percentage, RANK() OVER (PARTITION BY department_id ORDER BY completion_percentage DESC) AS rank FROM projects; SELECT project_id, department_id, completion_percentage, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY completion_percentage DESC) AS rank FROM projects; SELECT project_id, department_id, completion_percentage, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY completion_percentage DESC) AS rank FROM projects;SELECT project_id, department_id, completion_percentage, RANK() OVER (ORDER BY completion_percentage DESC) AS rank FROM projects;

Ranking functions like RANK(), DENSE_RANK(), and ROW_NUMBER() can be used to rank projects within a department based on completion percentage.

PostgreSQL Database Quiz Questions