Projects and Interview Preparation Coding Practice Challenges | CodeNivo

Question 1

Returning Only the Nth-Highest Salary Value

Accepted Answer

SELECT (
    SELECT DISTINCT salary
    FROM employees
    WHERE salary IS NOT NULL
    ORDER BY salary DESC
    OFFSET 2
    LIMIT 1
) AS third_highest_salary;

Answer

-- Practice query for Solving Common SQL Interview Problems

Question 2

Problem 3: Find and Remove Duplicate Rows Safely

Accepted Answer

WITH ranked_customers AS (
    SELECT
        customer_id,
        full_name,
        email,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY customer_id
        ) AS duplicate_number
    FROM customers
    WHERE email IS NOT NULL
)
SELECT
    customer_id,
    full_name,
    email,
    duplicate_number
FROM ranked_customers
WHERE duplicate_number > 1
ORDER BY email, customer_id;

Answer

-- Practice query for Solving Common SQL Interview Problems

Question 3

Final Project: Student Management Database

Accepted Answer

CREATE TABLE students (
    student_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE courses (
    course_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    course_code VARCHAR(20) NOT NULL UNIQUE,
    course_name VARCHAR(150) NOT NULL,
    capacity INTEGER NOT NULL CHECK (capacity >= 0)
);

CREATE TABLE enrollments (
    enrollment_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    student_id INTEGER NOT NULL REFERENCES students(student_id),
    course_id INTEGER NOT NULL REFERENCES courses(course_id),
    enrolled_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (student_id, course_id)
);

-- Useful indexes for common lookups and joins
CREATE INDEX idx_enrollments_course_id
ON enrollments (course_id);

CREATE INDEX idx_enrollments_student_enrolled
ON enrollments (student_id, enrolled_at DESC);

Answer

-- Practice query for SQL Best Practices and Final Project

Question 4

Exercise Solutions

Accepted Answer

-- Solution 1: Every student and their courses
SELECT
    s.full_name,
    c.course_code,
    c.course_name
FROM students AS s
LEFT JOIN enrollments AS e
    ON e.student_id = s.student_id
LEFT JOIN courses AS c
    ON c.course_id = e.course_id
ORDER BY s.full_name, c.course_code;

-- Solution 2: Active enrollments by course
SELECT
    c.course_code,
    c.course_name,
    COUNT(e.enrollment_id) AS active_enrollments
FROM courses AS c
LEFT JOIN enrollments AS e
    ON e.course_id = c.course_id
   AND e.status = 'enrolled'
GROUP BY c.course_id, c.course_code, c.course_name
ORDER BY c.course_code;

-- Solution 3: Students with simple GPA >= 3.5
SELECT
    s.student_id,
    s.full_name,
    ROUND(AVG(g.grade_points), 2) AS gpa
FROM students AS s
JOIN enrollments AS e
    ON e.student_id = s.student_id
   AND e.status = 'completed'
JOIN grades AS g
    ON g.letter_grade = e.final_grade
GROUP BY s.student_id, s.full_name
HAVING AVG(g.grade_points) >= 3.5
ORDER BY gpa DESC, s.full_name;

-- Solution 4: Completed credits for every student
SELECT
    s.student_id,
    s.full_name,
    COALESCE(
        SUM(c.credits) FILTER (
            WHERE e.status = 'completed'
        ),
        0
    ) AS completed_credits
FROM students AS s
LEFT JOIN enrollments AS e
    ON e.student_id = s.student_id
LEFT JOIN courses AS c
    ON c.course_id = e.course_id
GROUP BY s.student_id, s.full_name
ORDER BY s.student_id;

Answer

-- Practice query for Building a Student Management Database

Question 5

Practice Problem Solutions

Accepted Answer

-- Exercise 1: Second-highest distinct salary
SELECT (
    SELECT DISTINCT salary
    FROM employees
    WHERE salary IS NOT NULL
    ORDER BY salary DESC
    OFFSET 1
    LIMIT 1
) AS second_highest_salary;

-- Exercise 2: Every employee with the highest salary
SELECT employee_id, employee_name, salary
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);

-- Exercise 3: Duplicate non-NULL phone numbers
SELECT phone, COUNT(*) AS occurrence_count
FROM customers
WHERE phone IS NOT NULL
GROUP BY phone
HAVING COUNT(*) > 1;

-- Exercise 4: Newest staging row per external customer
WITH ranked_staging AS (
    SELECT
        staging_id,
        external_customer_id,
        updated_at,
        ROW_NUMBER() OVER (
            PARTITION BY external_customer_id
            ORDER BY updated_at DESC NULLS LAST, staging_id DESC
        ) AS row_num
    FROM customer_staging
)
SELECT *
FROM ranked_staging
WHERE row_num = 1;

-- Exercise 5: Customers with no orders
SELECT c.customer_id, c.full_name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
);

-- Exercise 6: Every customer and order count
SELECT
    c.customer_id,
    c.full_name,
    COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.full_name
ORDER BY c.customer_id;

-- Exercise 7: Monthly active customers and change
WITH monthly_counts AS (
    SELECT
        DATE_TRUNC('month', order_date)::date AS activity_month,
        COUNT(DISTINCT customer_id) AS active_customers
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)::date
)
SELECT
    activity_month,
    active_customers,
    active_customers - LAG(active_customers) OVER (
        ORDER BY activity_month
    ) AS change_from_previous_month
FROM monthly_counts
ORDER BY activity_month;

Answer

-- Practice query for Solving Common SQL Interview Problems

Question 6

Recording Payment Attempts

Accepted Answer

INSERT INTO payments
    (order_id, amount, status, payment_date, transaction_reference)
VALUES
    (1, 4900.00, 'succeeded', '2026-09-05 10:05:00+00', 'TXN-1001'),
    (2, 2800.00, 'succeeded', '2026-09-12 12:35:00+00', 'TXN-1002'),
    (3, 4200.00, 'succeeded', '2026-10-02 09:20:00+00', 'TXN-1003'),
    (4, 1200.00, 'pending',   '2026-10-10 16:50:00+00', 'TXN-1004'),
    (5, 1500.00, 'failed',    '2026-10-15 14:05:00+00', 'TXN-1005');

Answer

-- Practice query for Building an E-commerce Database

Question 7

Indexes and Query Performance

Accepted Answer

CREATE INDEX idx_enrollments_course_id
    ON enrollments(course_id);

EXPLAIN
SELECT
    student_id,
    course_id,
    status
FROM enrollments
WHERE course_id = 3;

Answer

-- Practice query for Building a Student Management Database

Question 8

Monthly Revenue Analytics

Accepted Answer

SELECT
    DATE_TRUNC('month', payment_date)::date AS revenue_month,
    SUM(amount) AS successful_payment_total
FROM payments
WHERE status = 'succeeded'
GROUP BY DATE_TRUNC('month', payment_date)::date
ORDER BY revenue_month;

Answer

-- Practice query for Building an E-commerce Database

Question 9

Problem 7: Find Customers with Consecutive Active Months

Accepted Answer

WITH monthly_activity AS (
    SELECT DISTINCT
        customer_id,
        DATE_TRUNC('month', order_date)::date AS activity_month
    FROM orders
),
activity_with_previous AS (
    SELECT
        customer_id,
        activity_month,
        LAG(activity_month) OVER (
            PARTITION BY customer_id
            ORDER BY activity_month
        ) AS previous_active_month
    FROM monthly_activity
)
SELECT
    customer_id,
    previous_active_month,
    activity_month
FROM activity_with_previous
WHERE activity_month =
      (previous_active_month + INTERVAL '1 month')::date
ORDER BY customer_id, activity_month;

Answer

-- Practice query for Solving Common SQL Interview Problems

Question 10

Practice Exercises With Solutions

Accepted Answer

-- Exercise 1
SELECT student_id, name
FROM students
WHERE is_active = TRUE
ORDER BY name, student_id;

-- Exercise 2
SELECT c.course_code, COUNT(e.enrollment_id) AS enrollment_count
FROM courses AS c
LEFT JOIN enrollments AS e
    ON e.course_id = c.course_id
GROUP BY c.course_id, c.course_code
ORDER BY c.course_code;

-- Exercise 3
SELECT s.student_id, s.name
FROM students AS s
WHERE NOT EXISTS (
    SELECT 1
    FROM enrollments AS e
    WHERE e.student_id = s.student_id
)
ORDER BY s.student_id;

-- Exercise 4
SELECT LOWER(email) AS normalized_email, COUNT(*) AS occurrences
FROM students
GROUP BY LOWER(email)
HAVING COUNT(*) > 1;

-- Exercise 5: start by inspecting the actual plan
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Answer

-- Practice query for SQL Best Practices and Final Project

Question 11

Verifying the Sample Data

Accepted Answer

SELECT
    s.full_name,
    c.course_code,
    c.course_name,
    e.status,
    e.final_grade,
    g.grade_points
FROM enrollments AS e
JOIN students AS s
    ON s.student_id = e.student_id
JOIN courses AS c
    ON c.course_id = e.course_id
LEFT JOIN grades AS g
    ON g.letter_grade = e.final_grade
ORDER BY s.full_name, c.course_code;

Answer

-- Practice query for Building a Student Management Database

Question 12

Finding Students Without Enrollments

Accepted Answer

SELECT
    s.student_id,
    s.full_name,
    s.email
FROM students AS s
WHERE NOT EXISTS (
    SELECT 1
    FROM enrollments AS e
    WHERE e.student_id = s.student_id
)
ORDER BY s.student_id;

Answer

-- Practice query for Building a Student Management Database

Question 13

Using Transactions for Order Placement

Accepted Answer

BEGIN;

-- Reserve one unit only if stock is available.
UPDATE inventory
SET
    stock_quantity = stock_quantity - 1,
    updated_at = CURRENT_TIMESTAMP
WHERE product_id = 5
  AND stock_quantity >= 1
RETURNING product_id, stock_quantity;

-- Continue only if the inventory update returned a row.
-- In application code, capture the generated order ID.
INSERT INTO orders (customer_id, status)
VALUES (4, 'pending')
RETURNING order_id;

-- Use the returned order_id in the following statement.
-- The example assumes the generated order ID is 6.
INSERT INTO order_items
    (order_id, product_id, quantity, unit_price)
VALUES
    (6, 5, 1, 500.00);

COMMIT;

Answer

-- Practice query for Building an E-commerce Database

Question 14

Exercise Solutions

Accepted Answer

-- Solution 1: All customers and their orders
SELECT
    c.customer_id,
    c.full_name,
    o.order_id,
    o.order_date,
    o.status
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_date;

-- Solution 2: Active products with fewer than 10 units
SELECT
    p.sku,
    p.product_name,
    i.stock_quantity
FROM products AS p
JOIN inventory AS i
    ON i.product_id = p.product_id
WHERE p.is_active = TRUE
  AND i.stock_quantity < 10
ORDER BY i.stock_quantity, p.product_name;

-- Solution 3: Successful payment totals by month
SELECT
    DATE_TRUNC('month', payment_date)::date AS revenue_month,
    SUM(amount) AS successful_payment_total
FROM payments
WHERE status = 'succeeded'
GROUP BY DATE_TRUNC('month', payment_date)::date
ORDER BY revenue_month;

-- Solution 4: Top three products by non-cancelled units ordered
SELECT
    p.product_id,
    p.product_name,
    COALESCE(SUM(oi.quantity), 0) AS units_ordered
FROM products AS p
LEFT JOIN order_items AS oi
    ON oi.product_id = p.product_id
LEFT JOIN orders AS o
    ON o.order_id = oi.order_id
   AND o.status <> 'cancelled'
WHERE oi.order_id IS NULL
   OR o.order_id IS NOT NULL
GROUP BY p.product_id, p.product_name
ORDER BY units_ordered DESC, p.product_name
LIMIT 3;

-- Solution 5: Customers without successful payments
SELECT
    c.customer_id,
    c.full_name,
    c.email
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    JOIN payments AS p
        ON p.order_id = o.order_id
    WHERE o.customer_id = c.customer_id
      AND p.status = 'succeeded'
)
ORDER BY c.customer_id;

Answer

-- Practice query for Building an E-commerce Database

Question 15

Identifying Inactive Customers

Accepted Answer

WITH customer_activity AS (
    SELECT
        c.customer_id,
        c.full_name,
        c.email,
        MAX(o.order_date) AS last_order_date
    FROM customers AS c
    LEFT JOIN orders AS o
        ON o.customer_id = c.customer_id
    GROUP BY c.customer_id, c.full_name, c.email
)
SELECT
    customer_id,
    full_name,
    email,
    last_order_date
FROM customer_activity
WHERE last_order_date IS NULL
   OR last_order_date < TIMESTAMPTZ '2026-10-01 00:00:00+00'
ORDER BY customer_id;

Answer

-- Practice query for Building an E-commerce Database

Question 16

Indexes and Query Performance

Accepted Answer

CREATE INDEX idx_orders_customer_date
    ON orders(customer_id, order_date DESC);

CREATE INDEX idx_orders_order_date
    ON orders(order_date);

CREATE INDEX idx_order_items_product
    ON order_items(product_id);

CREATE INDEX idx_payments_date
    ON payments(payment_date);

EXPLAIN
SELECT
    order_id,
    order_date,
    status
FROM orders
WHERE customer_id = 1
ORDER BY order_date DESC;

Answer

-- Practice query for Building an E-commerce Database

course_code	course_name	active_enrollments
CS101	Introduction to Programming	0
DB201	Database Systems	0
NW101	Computer Networks	0
OS301	Operating Systems	1

student_id	full_name	gpa
1	Aarav Sharma	3.50
2	Diya Verma	3.50

student_id	full_name	completed_credits
1	Aarav Sharma	7
2	Diya Verma	8
3	Kabir Singh	3
4	Meera Gupta	0

Projects and Interview Preparation Problems