A case study of dense_rank vs row_number

using-denserank-vs-rownumber.sql

DROP TABLE IF EXISTS user_courses;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS courses;

-- Create users table
CREATE TABLE IF NOT EXISTS users (
    id INT IDENTITY(1,1) PRIMARY KEY,
    user_name VARCHAR(100) NOT NULL
);

-- Create courses table
CREATE TABLE IF NOT EXISTS courses (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- Create user_courses junction table
CREATE TABLE IF NOT EXISTS user_courses (
    user_id INT NOT NULL,
    course_id INT NOT NULL,
    CONSTRAINT PK_user_courses PRIMARY KEY (user_id, course_id),
    CONSTRAINT FK_user_courses_user FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT FK_user_courses_course FOREIGN KEY (course_id) REFERENCES courses(id)
);

-- Insert sample users
INSERT INTO users (id, user_name)
SELECT 1, 'John Smith'
WHERE NOT EXISTS (SELECT 1 FROM users WHERE user_name = 'John Smith');

INSERT INTO users (id, user_name)
SELECT 2, 'Jane Doe'
WHERE NOT EXISTS (SELECT 1 FROM users WHERE user_name = 'Jane Doe');

INSERT INTO users (id, user_name)
SELECT 3, 'Bob Johnson'
WHERE NOT EXISTS (SELECT 1 FROM users WHERE user_name = 'Bob Johnson');

INSERT INTO users (id, user_name)
SELECT 4, 'Alice Brown'
WHERE NOT EXISTS (SELECT 1 FROM users WHERE user_name = 'Alice Brown');

INSERT INTO users (id, user_name)
SELECT 5, 'Charlie Wilson'
WHERE NOT EXISTS (SELECT 1 FROM users WHERE user_name = 'Charlie Wilson');

-- Insert sample courses
INSERT INTO courses (id, name)
SELECT 1, 'Introduction to SQL'
WHERE NOT EXISTS (SELECT 1 FROM courses WHERE name = 'Introduction to SQL');

INSERT INTO courses (id, name)
SELECT 2, 'Advanced Database Design'
WHERE NOT EXISTS (SELECT 1 FROM courses WHERE name = 'Advanced Database Design');

INSERT INTO courses (id, name)
SELECT 3, 'Data Analytics'
WHERE NOT EXISTS (SELECT 1 FROM courses WHERE name = 'Data Analytics');

INSERT INTO courses (id, name)
SELECT 4, 'Web Development'
WHERE NOT EXISTS (SELECT 1 FROM courses WHERE name = 'Web Development');

INSERT INTO courses (id, name)
SELECT 5, 'Machine Learning'
WHERE NOT EXISTS (SELECT 1 FROM courses WHERE name = 'Machine Learning');

-- Insert sample user_courses relationships
INSERT INTO user_courses (user_id, course_id)
SELECT 1, 1
WHERE NOT EXISTS (SELECT 1 FROM user_courses WHERE user_id = 1 AND course_id = 1);

INSERT INTO user_courses (user_id, course_id)
SELECT 1, 2
WHERE NOT EXISTS (SELECT 1 FROM user_courses WHERE user_id = 1 AND course_id = 2);

INSERT INTO user_courses (user_id, course_id)
SELECT 1, 3
WHERE NOT EXISTS (SELECT 1 FROM user_courses WHERE user_id = 1 AND course_id = 3);

INSERT INTO user_courses (user_id, course_id)
SELECT 2, 1
WHERE NOT EXISTS (SELECT 1 FROM user_courses WHERE user_id = 2 AND course_id = 1);

INSERT INTO user_courses (user_id, course_id)
SELECT 2, 4
WHERE NOT EXISTS (SELECT 1 FROM user_courses WHERE user_id = 2 AND course_id = 4);

INSERT INTO user_courses (user_id, course_id)
SELECT 3, 1
WHERE NOT EXISTS (SELECT 1 FROM user_courses WHERE user_id = 3 AND course_id = 1);

INSERT INTO user_courses (user_id, course_id)
SELECT 3, 2
WHERE NOT EXISTS (SELECT 1 FROM user_courses WHERE user_id = 3 AND course_id = 2);

INSERT INTO user_courses (user_id, course_id)
SELECT 3, 3
WHERE NOT EXISTS (SELECT 1 FROM user_courses WHERE user_id = 3 AND course_id = 3);

INSERT INTO user_courses (user_id, course_id)
SELECT 3, 5
WHERE NOT EXISTS (SELECT 1 FROM user_courses WHERE user_id = 3 AND course_id = 5);

INSERT INTO user_courses (user_id, course_id)
SELECT 4, 4
WHERE NOT EXISTS (SELECT 1 FROM user_courses WHERE user_id = 4 AND course_id = 4);

INSERT INTO user_courses (user_id, course_id)
SELECT 4, 5
WHERE NOT EXISTS (SELECT 1 FROM user_courses WHERE user_id = 4 AND course_id = 5);

INSERT INTO user_courses (user_id, course_id)
SELECT 5, 1
WHERE NOT EXISTS (SELECT 1 FROM user_courses WHERE user_id = 5 AND course_id = 5);

SELECT 
  *
FROM (
  SELECT 
    u.id as user_id, -- may have dupes
    u.user_name,
    c.id as course_id,
    c.name, 
    ROW_NUMBER() OVER (
        ORDER BY
            u.id DESC
    ) As RowNum
  FROM users u
  JOIN user_courses uc on uc.user_id = u.id
  JOIN courses c on c.id = uc.course_id
) AS ConstrainedResult
WHERE
    RowNum > 0
    AND RowNum <= 5;
    
SELECT 
  DISTINCT user_id,
  user_name, 
  course_id, 
  name,
  RankNum
FROM (
  SELECT 
    u.id as user_id,
    u.user_name,
    c.id as course_id,
    c.name, 
    DENSE_RANK() OVER (
        ORDER BY
            u.id ASC
    ) As RankNum
  FROM users u
  JOIN user_courses uc on uc.user_id = u.id
  JOIN courses c on c.id = uc.course_id
) AS ConstrainedResult
WHERE
    RankNum > 0
    AND RankNum <= 2;
    
select * from users;
select * from user_courses;
select * from courses;

-- retrieve all
SELECT 
  u.id as user_id, 
  u.user_name, 
  c.id as course_id, 
  c.name as course_name
FROM users u
LEFT JOIN User_Courses uc ON u.id = uc.user_id
LEFT JOIN Courses c ON uc.course_id = c.id

-- basic pagination with LIMIT OFFSET
SELECT 
  u.id as user_id, 
  u.user_name, 
  c.id as course_id, 
  c.name as course_name
FROM users u
LEFT JOIN User_Courses uc ON u.id = uc.user_id
LEFT JOIN Courses c ON uc.course_id = c.id
LIMIT 2 OFFSET 0

-- try row_number()
SELECT * FROM (
    SELECT 
    u.id as user_id, 
    u.user_name, 
    c.id as course_id, 
    c.name as course_name, 
    ROW_NUMBER() OVER (
        ORDER BY
            u.id ASC
    ) As RowNum
    FROM users u
    LEFT JOIN User_Courses uc ON u.id = uc.user_id
    LEFT JOIN Courses c ON uc.course_id = c.id
) as t
WHERE
    RowNum > 0
    AND RowNum <= 2;

-- performant query
SELECT
  u.id as user_id,
  u.user_name,
  c.id as course_id,
  c.name
FROM users u
JOIN user_courses uc on uc.user_id = u.id
JOIN courses c on c.id = uc.course_id
WHERE u.id IN (
    SELECT 
        DISTINCT user_id
    FROM (
        SELECT 
            u.id as user_id,
            DENSE_RANK() OVER (
                ORDER BY
                    u.id ASC
            ) As RankNum
        FROM users u
        JOIN user_courses uc on uc.user_id = u.id
        JOIN courses c on c.id = uc.course_id
    ) AS ConstrainedResult
    WHERE
        RankNum > 0
        AND RankNum <= 2
);