PostgreSQL SQL Interview Problems: Basics to Advanced

Question 1

Recyclable and Low Fat Products

LeetCode #1757

Schema: products(product_id integer PRIMARY KEY, low_fats enum('Y','N'), recyclable enum('Y','N')). All columns are non-null; each flag uses 'Y' for yes and 'N' for no.

Write a PostgreSQL query that returns product_id for every product marked both low fat and recyclable. Row order does not matter.

Accepted Answer

SELECT product_id
FROM products
WHERE low_fats = 'Y'
  AND recyclable = 'Y';

SELECT chooses the output columns; FROM names the input table. WHERE keeps rows whose condition is true. AND requires both comparisons to be true. Single quotes delimit text values.

Question 2

Big Countries

LeetCode #595

Schema: world(name varchar PRIMARY KEY, continent varchar, area integer, population integer, gdp bigint). All columns are non-null; area is measured in square kilometers.

Return name, population, and area for countries with an area of at least 3,000,000 square kilometers or a population of at least 25,000,000. Meeting either threshold is enough. Row order does not matter.

Accepted Answer

SELECT name, population, area
FROM world
WHERE area >= 3000000
   OR population >= 25000000;

OR accepts a row when either condition is true, including when both are true. >= includes the threshold itself.

Question 3

Find Customer Referee

LeetCode #584

Schema: customer(id integer PRIMARY KEY, name varchar NOT NULL, referee_id integer NULL). referee_id identifies the referring customer; null means there was no referral. Names need not be unique.

Return name for every customer except those referred by customer 2. Include customers with no referrer, and preserve separate customers even when their names match. Row order does not matter.

Accepted Answer

SELECT name FROM customer WHERE referee_id <> 2 OR referee_id IS NULL; <> means unequal. A comparison with null produces unknown, which WHERE does not keep. IS NULL explicitly includes customers without a referrer; = NULL would not work.

Question 4

Article Views I

LeetCode #1148

Schema: views(article_id integer, author_id integer, viewer_id integer, view_date date). All columns are non-null. There is no primary key, and duplicate rows are allowed. Equal author and viewer IDs refer to the same person.

Return each author who has viewed an article they wrote, once only. Name the output column id and sort it ascending.

Accepted Answer

SELECT DISTINCT author_id AS id
FROM views
WHERE author_id = viewer_id
ORDER BY id ASC;

The equality selects self-views. DISTINCT removes repeated output values, and AS gives the column its required name. ORDER BY ... ASC sorts from smallest to largest; sorting is not guaranteed without it.

Question 5

Invalid Tweets

LeetCode #1683

Schema: tweets(tweet_id integer PRIMARY KEY, content varchar NOT NULL). Content contains only letters, digits, exclamation marks, and spaces.

A tweet fails this exercise's length rule when its content contains more than 15 characters, counting spaces and punctuation. Return tweet_id for all such tweets. A length of exactly 15 is valid. Row order does not matter.

Accepted Answer

SELECT tweet_id
FROM tweets
WHERE char_length(content) > 15;

char_length counts characters, including spaces. The strict > comparison excludes content of exactly 15 characters. Character length and byte length are different measurements.

Question 6

Not Boring Movies

LeetCode #620

Schema: cinema(id integer PRIMARY KEY, movie varchar, description varchar, rating double precision). All columns are non-null. Ratings range from 0 to 10 and have at most two decimal places.

Return id, movie, description, and rating for movies whose ID is odd and whose description differs from the exact text 'boring'. Sort by rating descending; tied ratings may appear in either order.

Accepted Answer

SELECT id, movie, description, rating FROM cinema WHERE id % 2 <> 0 AND description <> 'boring' ORDER BY rating DESC; % returns the division remainder. An integer is odd when its remainder after division by 2 is nonzero. DESC puts higher ratings first. Both row conditions must hold.

Question 7

Triangle Judgement

LeetCode #610

Schema: triangle(x integer, y integer, z integer, PRIMARY KEY (x, y, z)). All three values are positive, non-null segment lengths.

For each row, return x, y, z, and triangle. Set triangle to 'Yes' if the segments can form a triangle with positive area, otherwise 'No'. A straight-line arrangement does not qualify. Row order does not matter.

Accepted Answer

SELECT x, y, z,
       CASE
           WHEN x::bigint + y > z
            AND x::bigint + z > y
            AND y::bigint + z > x
           THEN 'Yes'
           ELSE 'No'
       END AS triangle
FROM triangle;

Each pair of sides must sum to more than the remaining side. CASE WHEN ... THEN ... ELSE ... END chooses a value for each row. ::bigint converts one operand before addition, preventing overflow when adding two integer lengths.

Question 8

Find Followers Count

LeetCode #1729

Schema: followers(user_id integer, follower_id integer, PRIMARY KEY (user_id, follower_id)). Both columns are non-null. Each row means that follower_id follows user_id; the pair cannot repeat.

For each user_id present in this table, return user_id and its number of followers as followers_count. Sort by user_id ascending. Users with no follower rows are outside the input.

Accepted Answer

SELECT user_id, COUNT(*) AS followers_count
FROM followers
GROUP BY user_id
ORDER BY user_id;

GROUP BY gathers rows with the same user ID into one group. COUNT(*) counts every row in that group. The key ensures each follower contributes once per user. ORDER BY defaults to ascending order.

Question 9

Number of Unique Subjects Taught by Each Teacher

LeetCode #2356

Schema: teacher(teacher_id integer, subject_id integer, dept_id integer, PRIMARY KEY (subject_id, dept_id)). All columns are non-null. Each row assigns a subject in a department to a teacher. A teacher may teach the same subject in several departments.

Return teacher_id and cnt, counting different subjects taught by each teacher present in the table. Count a subject once per teacher regardless of departments. Row order does not matter.

Accepted Answer

SELECT teacher_id, COUNT(DISTINCT subject_id) AS cnt
FROM teacher
GROUP BY teacher_id;

COUNT(DISTINCT subject_id) counts different non-null subject IDs within each teacher's group. Counting rows instead would count the same subject again when that teacher teaches it in another department.

Question 10

Classes More Than 5 Students

LeetCode #596

Schema: courses(student varchar, class varchar, PRIMARY KEY (student, class)). Both columns are non-null. A row records one student's enrollment in one class.

Return the single column class for classes with at least five enrolled students. Exactly five qualifies despite the historical problem title. Row order does not matter.

Accepted Answer

SELECT class
FROM courses
GROUP BY class
HAVING COUNT(*) >= 5;

HAVING filters groups after their aggregates are calculated; WHERE filters input rows before grouping. The enrollment key prevents duplicate students within a class, so counting rows gives the student count. The inclusive threshold keeps groups of exactly five.

Question 11

Game Play Analysis I

LeetCode #511

Schema: activity(player_id integer, device_id integer, event_date date, games_played integer, PRIMARY KEY (player_id, event_date)). All columns are non-null. Each row records a login day; games_played can be zero.

Return player_id and first_login, the earliest recorded login date for each player. A day with zero games still counts as a login. Row order does not matter.

Accepted Answer

SELECT player_id, MIN(event_date) AS first_login
FROM activity
GROUP BY player_id;

MIN returns the smallest value in a group; for dates, that is the earliest date. Grouping by player keeps each player's history separate. No games-played filter is needed because a row already represents a login.

Question 12

Find Total Time Spent by Each Employee

LeetCode #1741

Schema: employees(emp_id integer, event_day date, in_time integer, out_time integer, PRIMARY KEY (emp_id, event_day, in_time)). All columns are non-null. Times are minutes within the same day, from 1 through 1440; entry precedes exit. An employee's intervals on a day do not overlap.

Return day, emp_id, and total_time for each employee-day in the table. Sum minutes spent inside across every visit that day. Row order does not matter.

Accepted Answer

SELECT event_day AS day, emp_id,
       SUM(out_time - in_time) AS total_time
FROM employees
GROUP BY event_day, emp_id;

Subtract entry from exit for each visit, then SUM those durations. Grouping by both date and employee creates a separate total for each employee-day. This preserves several visits on the same day without combining different employees or dates.

Question 13

User Activity for the Past 30 Days I

LeetCode #1141

Schema: activity(user_id integer, session_id integer, activity_date date, activity_type enum('open_session','end_session','scroll_down','send_message')). All columns are non-null. There is no primary key; duplicate rows are allowed. Each session belongs to one user.

For the 30 calendar days ending July 27, 2019, inclusive, return day and active_users. Any listed activity makes a user active that day; count each user once per day. Omit days with no activity. Row order does not matter.

Accepted Answer

SELECT activity_date AS day,
       COUNT(DISTINCT user_id) AS active_users
FROM activity
WHERE activity_date >= DATE '2019-06-28'
  AND activity_date <= DATE '2019-07-27'
GROUP BY activity_date;

DATE 'YYYY-MM-DD' creates a date literal. June 28 through July 27 contains 30 dates with both endpoints included. Filter the period first, then count distinct users per date. Duplicate activities and multiple sessions cannot inflate that count.

Question 14

Biggest Single Number

LeetCode #619

Schema: mynumbers(num integer NOT NULL). There is no primary key; values may repeat, and the table may be empty.

Find the largest integer that occurs exactly once in the entire table. Return exactly one row with column num. If every value repeats, or the table is empty, that row must contain null.

Accepted Answer

WITH singles AS (
    SELECT num
    FROM mynumbers
    GROUP BY num
    HAVING COUNT(*) = 1
)
SELECT MAX(num) AS num
FROM singles;

WITH names an intermediate query result, called a common table expression (CTE). singles contains values occurring once. The outer MAX selects the largest. An aggregate without GROUP BY returns one row even for empty input; MAX then returns null.

Question 15

Fix Names in a Table

LeetCode #1667

Schema: users(user_id integer PRIMARY KEY, name varchar NOT NULL). Names contain only uppercase and lowercase letters.

Return user_id and name, formatting each name with an uppercase first character and lowercase remaining characters. Preserve the stored names; return the formatted values as query output. Sort by user_id ascending.

Accepted Answer

SELECT user_id,
       upper(substring(name FROM 1 FOR 1))
       || lower(substring(name FROM 2)) AS name
FROM users
ORDER BY user_id;

substring uses positions starting at 1; FOR 1 takes one character, while omitting FOR takes the rest. upper and lower change letter case. || concatenates the first character with the remaining text. A one-character name has an empty remaining substring.

Question 16

Patients With a Condition

LeetCode #1527

Schema: patients(patient_id integer PRIMARY KEY, patient_name varchar NOT NULL, conditions varchar NOT NULL). conditions holds space-separated condition codes; an empty string means no codes.

Return patient_id, patient_name, and conditions for patients with at least one code beginning with the exact uppercase prefix DIAB1. A matching sequence inside a code, rather than at its beginning, does not qualify. Row order does not matter.

Accepted Answer

SELECT patient_id, patient_name, conditions
FROM patients
WHERE conditions ~ '(^| )DIAB1';

PostgreSQL's ~ tests a case-sensitive regular expression. ^ means the start of the string; the other alternative is a literal space. Parentheses group those alternatives, and | means either one. Thus DIAB1 must start a code. No ending boundary is required because longer codes with that prefix qualify.

Question 17

Find Users With Valid E-Mails

LeetCode #1517

Schema: users(user_id integer PRIMARY KEY, name varchar NOT NULL, mail varchar NOT NULL).

Return user_id, name, and mail for addresses meeting this exercise's rules: the part before @ starts with an English letter (A–Z or a–z); its remaining characters may be letters, digits, underscores, periods, or hyphens. The suffix must be exactly lowercase @leetcode.com. No other characters are allowed. Row order does not matter.

Accepted Answer

SELECT user_id, name, mail
FROM users
WHERE mail ~ '^[A-Za-z][A-Za-z0-9_.-]*@leetcode[.]com$';

^ and $ require the entire address to match. Brackets describe one allowed character; the first class requires a letter. * permits zero or more characters from the next class, allowing a one-letter prefix. A final hyphen in a class is literal, and [.] matches a literal period. Case-sensitive ~ keeps the domain lowercase.

Question 18

Group Sold Products By The Date

LeetCode #1484

Schema: activities(sell_date date NOT NULL, product varchar NOT NULL). There is no primary key, and duplicate rows are allowed. Each row records a product sold on that date.

For each sale date, return sell_date, num_sold (the number of different products), and products (their distinct names in lexicographic order, joined with commas and no added spaces). Preserve product-name spelling. Sort rows by sell_date ascending.

Accepted Answer

SELECT sell_date,
       COUNT(DISTINCT product) AS num_sold,
       string_agg(DISTINCT product, ',' ORDER BY product) AS products
FROM activities
GROUP BY sell_date
ORDER BY sell_date;

string_agg joins text values using its second argument as the separator. Its internal DISTINCT removes repeated names, and its internal ORDER BY sorts names within each day's string. The final ORDER BY sorts result rows. Both aggregates operate on the same daily product set.

Question 19

Product Sales Analysis I — #1068

Schema (unquoted PostgreSQL identifiers; all columns NOT NULL):

Sales(sale_id integer, product_id integer, year integer, quantity integer, price integer)
  PRIMARY KEY (sale_id, year); product_id references Product.product_id
Product(product_id integer PRIMARY KEY, product_name text)

Return one row for every sale, with columns product_name, year, price in that order. The price is per unit; do not multiply it by quantity. Keep duplicate output rows when separate sales have the same values. Any row order is accepted.

Accepted Answer

SELECT p.product_name, s.year, s.price
FROM Sales AS s
JOIN Product AS p ON p.product_id = s.product_id;

The join adds the product name to each sale. The product key ensures each sale matches one product.

Question 20

Replace Employee ID With The Unique Identifier — #1378

Schema (unquoted PostgreSQL identifiers; all columns NOT NULL):

Employees(id integer PRIMARY KEY, name text)
EmployeeUNI(id integer, unique_id integer)
  PRIMARY KEY (id, unique_id); id references Employees.id

Return columns unique_id, name for every employee and matching identifier. If an employee has no mapping, include their name with NULL as unique_id. Preserve all mappings allowed by the composite key. Any row order is accepted.

Accepted Answer

SELECT u.unique_id, e.name
FROM Employees AS e
LEFT JOIN EmployeeUNI AS u ON u.id = e.id;

LEFT JOIN retains employees without a match and fills the mapping columns with NULL.

Question 21

Employee Bonus — #577

Schema (unquoted PostgreSQL identifiers; all columns NOT NULL except supervisor):

Employee(empId integer PRIMARY KEY, name text, supervisor integer, salary integer)
Bonus(empId integer PRIMARY KEY, bonus integer)
  empId references Employee.empId

An employee may have no Bonus row. Return columns name, bonus for employees whose bonus is strictly below 1000 or who have no Bonus row. Show a missing bonus as NULL. Any row order is accepted.

Accepted Answer

SELECT e.name, b.bonus
FROM Employee AS e
LEFT JOIN Bonus AS b ON b.empId = e.empId
WHERE b.bonus < 1000 OR b.bonus IS NULL;

The NULL branch keeps employees without a bonus row. A comparison with NULL alone would not include them.

Question 22

Project Employees I — #1075

Schema (unquoted PostgreSQL identifiers; all columns NOT NULL):

Project(project_id integer, employee_id integer)
  PRIMARY KEY (project_id, employee_id); employee_id references Employee.employee_id
Employee(employee_id integer PRIMARY KEY, name text, experience_years integer)

For each project appearing in Project, calculate its assigned employees’ mean experience in years. Return columns project_id, average_years, rounding the mean to two decimal places. Any row order is accepted.

Accepted Answer

SELECT p.project_id,
       ROUND(AVG(e.experience_years), 2) AS average_years
FROM Project AS p
JOIN Employee AS e ON e.employee_id = p.employee_id
GROUP BY p.project_id;

AVG(integer) returns numeric in PostgreSQL, retaining fractional years. Round the final average.

Question 23

Employees Earning More Than Their Managers — #181

Schema (unquoted PostgreSQL identifiers; all columns NOT NULL except managerId):

Employee(id integer PRIMARY KEY, name text, salary integer, managerId integer)

managerId identifies the employee’s manager; NULL means no manager. Return the names of employees whose salary is strictly greater than their manager’s salary. Employees without a matching manager do not qualify. The sole output column must be named Employee with that capitalization. Keep separate qualifying employees even when their names match. Any row order is accepted.

Accepted Answer

SELECT worker.name AS "Employee"
FROM Employee AS worker
JOIN Employee AS manager ON manager.id = worker.managerId
WHERE worker.salary > manager.salary;

Aliases give the same table two roles. Quoting the output alias preserves its capital E in PostgreSQL.

Question 24

Managers with at Least 5 Direct Reports — #570

Schema (unquoted PostgreSQL identifiers; all columns NOT NULL except managerId):

Employee(id integer PRIMARY KEY, name text, department text, managerId integer)

managerId identifies an employee’s manager; NULL means none. Nobody manages themself. Return a name column containing each manager who has at least five direct reports, across all departments. Count only employees pointing directly to that manager. Different managers may share a name and must remain separate result rows. Any row order is accepted.

Accepted Answer

SELECT manager.name
FROM Employee AS manager
JOIN Employee AS report ON report.managerId = manager.id
GROUP BY manager.id, manager.name
HAVING COUNT(*) >= 5;

Group by manager identity so namesakes do not share a count. HAVING filters the completed groups.

Question 25

The Number of Employees Which Report to Each Employee — #1731

Schema (unquoted PostgreSQL identifiers; all columns NOT NULL except reports_to):

Employees(employee_id integer PRIMARY KEY, name text, reports_to integer, age integer)

reports_to identifies another employee who is the direct manager; NULL means none. Include only employees with at least one direct report. Return columns employee_id, name, reports_count, average_age: the manager’s identity, direct-report count, and those reports’ mean age rounded to the nearest integer (a half rounds up). Order by employee_id ascending.

Accepted Answer

SELECT manager.employee_id,
       manager.name,
       COUNT(*) AS reports_count,
       ROUND(AVG(report.age)) AS average_age
FROM Employees AS manager
JOIN Employees AS report ON report.reports_to = manager.employee_id
GROUP BY manager.employee_id, manager.name
ORDER BY manager.employee_id;

Each joined row represents one direct report. Average the reports’ ages, excluding the manager’s own age.

Question 26

Customers Who Never Order — #183

Schema (unquoted PostgreSQL identifiers; all columns NOT NULL):

Customers(id integer PRIMARY KEY, name text)
Orders(id integer PRIMARY KEY, customerId integer)
  customerId references Customers.id

Return the name of each customer with no order. The sole output column must be named Customers with that capitalization. A customer may have many orders, and different customers may share a name. Preserve one result row per qualifying customer. Any row order is accepted.

Accepted Answer

SELECT c.name AS "Customers"
FROM Customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM Orders AS o
    WHERE o.customerId = c.id
);

The correlated subquery checks for an order belonging to the current customer. NOT EXISTS keeps customers for whom it finds none.

Question 27

Customer Who Visited but Did Not Make Any Transactions — #1581

Schema (unquoted PostgreSQL identifiers; all columns NOT NULL):

Visits(visit_id integer PRIMARY KEY, customer_id integer)
Transactions(transaction_id integer PRIMARY KEY, visit_id integer, amount integer)
  visit_id references Visits.visit_id

A customer can visit repeatedly, and a visit can have multiple transactions. For each customer with at least one visit having no transactions, return columns customer_id, count_no_trans: the customer ID and number of such visits. A zero-amount transaction still counts as a transaction. Omit customers with no qualifying visits. Any row order is accepted.

Accepted Answer

SELECT v.customer_id, COUNT(*) AS count_no_trans
FROM Visits AS v
WHERE NOT EXISTS (
    SELECT 1
    FROM Transactions AS t
    WHERE t.visit_id = v.visit_id
)
GROUP BY v.customer_id;

First retain visits with no matching transaction, then count those visits per customer. Multiple transactions cannot multiply the count.

Question 28

Employees Whose Manager Left the Company — #1978

Schema (unquoted PostgreSQL identifiers; all columns NOT NULL except manager_id):

Employees(employee_id integer PRIMARY KEY, name text, manager_id integer, salary integer)

NULL manager_id means no manager was assigned. When a manager leaves, their row is removed but reports keep that manager’s ID; therefore manager_id is not constrained to an existing row. Return an employee_id column for employees with salary strictly below 30000 whose assigned manager is absent. Order by employee_id ascending.

Accepted Answer

SELECT e.employee_id
FROM Employees AS e
WHERE e.salary < 30000
  AND e.manager_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM Employees AS manager
      WHERE manager.employee_id = e.manager_id
  )
ORDER BY e.employee_id;

The explicit NULL check distinguishes an absent assigned manager from having no manager.

Question 29

Rising Temperature — #197

Schema (unquoted PostgreSQL identifiers; all columns NOT NULL):

Weather(id integer PRIMARY KEY, recordDate date UNIQUE, temperature integer)

Return an id column for weather rows whose temperature is strictly higher than the temperature on the immediately preceding calendar date. Dates may be missing: without a row for yesterday, a row does not qualify. IDs need not follow date order. Any row order is accepted.

Accepted Answer

SELECT today.id
FROM Weather AS today
JOIN Weather AS yesterday
  ON today.recordDate = yesterday.recordDate + 1
WHERE today.temperature > yesterday.temperature;

Adding an integer to a PostgreSQL date advances it by that many days. Join on calendar dates rather than adjacent IDs or adjacent observed rows.

Question 30

Average Time of Process per Machine — #1661

Schema (unquoted PostgreSQL identifiers; all columns NOT NULL):

Activity(machine_id integer, process_id integer, activity_type text, timestamp double precision)
  PRIMARY KEY (machine_id, process_id, activity_type)

activity_type is either start or end; timestamp stores seconds, not a SQL timestamp. Each machine/process pair has exactly one event of each type, with start time less than or equal to end time. Each represented machine has at least one process. Return columns machine_id, processing_time: the mean of its process durations (end minus start), rounded to three decimal places. Any row order is accepted.

Accepted Answer

SELECT started.machine_id,
       ROUND(AVG(finished.timestamp::numeric - started.timestamp::numeric), 3)
           AS processing_time
FROM Activity AS started
JOIN Activity AS finished
  ON finished.machine_id = started.machine_id
 AND finished.process_id = started.process_id
 AND finished.activity_type = 'end'
WHERE started.activity_type = 'start'
GROUP BY started.machine_id;

Pair events by both IDs so processes on different machines stay separate. Cast seconds to numeric for decimal-place rounding, then round the final mean.

Question 31

LeetCode #1795 — Rearrange Products Table

Write a PostgreSQL query for this schema:

products(product_id integer PRIMARY KEY, store1 integer NULL,
         store2 integer NULL, store3 integer NULL)

Each store column holds that store's price for the product. NULL means the store doesn't offer it. Return one row for every available product/store combination, with columns product_id, store, price. Use the exact store labels store1, store2, and store3. A zero price is available and must remain in the result. Any row order is acceptable.

For example, product 42 with prices (NULL, 0, 18) contributes (42, 'store2', 0) and (42, 'store3', 18).

Accepted Answer

SELECT product_id, 'store1' AS store, store1 AS price
FROM products
WHERE store1 IS NOT NULL
UNION ALL
SELECT product_id, 'store2' AS store, store2 AS price
FROM products
WHERE store2 IS NOT NULL
UNION ALL
SELECT product_id, 'store3' AS store, store3 AS price
FROM products
WHERE store3 IS NOT NULL;

Each branch turns one store column into rows. UNION ALL appends the branches without removing rows. Filter with IS NOT NULL so a price of zero stays available. The first branch sets the output column names.

Question 32

LeetCode #1907 — Count Salary Categories

Write a PostgreSQL query for:

accounts(account_id integer PRIMARY KEY, income integer NOT NULL)

Count accounts in these monthly income categories: Low Salary for income below 20000, Average Salary for income from 20000 through 50000 inclusive, and High Salary for income above 50000. Return columns category, accounts_count.

Always return all three category rows, including zero counts. An empty table therefore produces three zeros. Any row order is acceptable. For example, incomes 20000 and 50000 both belong to Average Salary.

Accepted Answer

SELECT 'Low Salary' AS category,
       COUNT(CASE WHEN income < 20000 THEN 1 END) AS accounts_count
FROM accounts
UNION ALL
SELECT 'Average Salary' AS category,
       COUNT(CASE WHEN income BETWEEN 20000 AND 50000 THEN 1 END) AS accounts_count
FROM accounts
UNION ALL
SELECT 'High Salary' AS category,
       COUNT(CASE WHEN income > 50000 THEN 1 END) AS accounts_count
FROM accounts;

A CASE without ELSE produces NULL for nonmatching accounts, and COUNT ignores those NULLs. Each aggregate has no GROUP BY, so it returns one row even when no accounts match or the table is empty. UNION ALL keeps the three category rows.

Question 33

LeetCode #1211 — Queries Quality and Percentage

Write a PostgreSQL query for:

queries(query_name text NULL, result text NULL,
        position integer NOT NULL, rating integer NOT NULL)

There is no key; duplicate rows are separate observations. position is 1–500 and rating is 1–5. For each query_name, return query_name, quality, poor_query_percentage. quality is the mean of each observation's rating / position. poor_query_percentage is 100 times the fraction of observations with a rating below 3. Round both metrics to two decimal places. Treat NULL query names as one group if present. Any row order is acceptable.

For example, observations with (position, rating) values (2, 3) and (4, 2) have quality 1.00 and poor-query percentage 50.00.

Accepted Answer

SELECT query_name,
       ROUND(AVG(rating::numeric / position), 2) AS quality,
       ROUND(100 * AVG(CASE WHEN rating < 3 THEN 1::numeric ELSE 0 END), 2)
           AS poor_query_percentage
FROM queries
GROUP BY query_name;

Cast before dividing: integer division would discard fractional parts. Average the individual ratios, rather than dividing the summed ratings by the summed positions. The numeric 1/0 expression makes its average the poor-query fraction. Round only the final metrics; keep duplicate observations in both calculations.

Question 34

LeetCode #1633 — Percentage of Users Attended a Contest

Write a PostgreSQL query for:

users(user_id integer PRIMARY KEY, user_name text NOT NULL)
register(contest_id integer, user_id integer,
         PRIMARY KEY (contest_id, user_id))

register.user_id references users.user_id; key columns are non-NULL. A registration row means that user registered for that contest. For each contest appearing in register, return contest_id, percentage: the percentage of all users who registered for it, rounded to two decimal places. Count registered users, regardless of whether they later attended.

Sort by percentage descending, then contest_id ascending. If there are no registrations, return no rows. For example, two registrations among five total users give 40.00 percent.

Accepted Answer

SELECT contest_id,
       ROUND(100::numeric * COUNT(*) / (SELECT COUNT(*) FROM users), 2)
           AS percentage
FROM register
GROUP BY contest_id
ORDER BY percentage DESC, contest_id ASC;

The composite key allows each user to contribute at most once per contest. The scalar subquery counts all users, including those without registrations. Casting to numeric preserves fractional percentages. Every returned contest has a valid registered user, so its denominator is positive.

Question 35

LeetCode #1193 — Monthly Transactions I

Write a PostgreSQL query for:

transactions(id integer PRIMARY KEY, country text NULL,
             state text NOT NULL, amount integer NOT NULL,
             trans_date date NOT NULL)

state is either approved or declined. For each calendar month and country present in the data, return these columns: month, country, trans_count, approved_count, trans_total_amount, approved_total_amount. Format month as YYYY-MM. The counts and amounts cover all transactions and approved transactions respectively.

A month/country group with no approvals needs zero for both approved metrics. Keep NULL countries as their own group within each month. Any row order is acceptable; do not generate absent month/country combinations.

Accepted Answer

SELECT TO_CHAR(DATE_TRUNC('month', trans_date::timestamp), 'YYYY-MM') AS month,
       country,
       COUNT(*) AS trans_count,
       COUNT(CASE WHEN state = 'approved' THEN 1 END) AS approved_count,
       SUM(amount) AS trans_total_amount,
       SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END)
           AS approved_total_amount
FROM transactions
GROUP BY DATE_TRUNC('month', trans_date::timestamp), country;

DATE_TRUNC makes one period key for every month, including its year; TO_CHAR formats that key for output. Conditional aggregates keep declined transactions in the overall totals while excluding them from approved totals. The explicit zero branch prevents a NULL approved sum when a group has no approvals.

Question 36

LeetCode #1327 — List the Products Ordered in a Period

Write a PostgreSQL query for:

products(product_id integer PRIMARY KEY, product_name text NOT NULL,
         product_category text NOT NULL)
orders(product_id integer NOT NULL, order_date date NOT NULL,
       unit integer NOT NULL)

orders.product_id references products.product_id. orders has no key and can contain duplicate rows; each row contributes its units. Return product_name, unit for each product whose total ordered units in February 2020 are at least 100. The output unit is that product's February total. Include February 1 and February 29, and exclude dates outside the month.

Product names need not be unique: keep different products separate even if their names match. Any row order is acceptable.

Accepted Answer

SELECT p.product_name, SUM(o.unit) AS unit
FROM products AS p
JOIN orders AS o ON o.product_id = p.product_id
WHERE o.order_date >= DATE '2020-02-01'
  AND o.order_date < DATE '2020-03-01'
GROUP BY p.product_id, p.product_name
HAVING SUM(o.unit) >= 100;

Filter the full February interval before summing. The upper boundary includes leap day without needing the month's final date in the query. Group by product identity so equal names don't merge. HAVING applies the minimum to each product's total; duplicate order rows still contribute.

Question 37

LeetCode #1251 — Average Selling Price

Write a PostgreSQL query for:

prices(product_id integer, start_date date, end_date date,
       price integer NOT NULL,
       PRIMARY KEY (product_id, start_date, end_date))
unitssold(product_id integer NOT NULL, purchase_date date NOT NULL,
          units integer NOT NULL)

Use these unquoted PostgreSQL table names; unitssold corresponds to source table UnitsSold. Price intervals include both endpoints, have non-NULL keys, and never overlap for one product. Each sale belongs to exactly one interval for its product. Units are positive; duplicate sale rows count separately.

Return product_id, average_price for every product in prices. Calculate total sales revenue divided by total sold units, rounding to two decimal places. A product with no sales has average price 0. Any row order is acceptable. For example, selling 2 units at 12 and 1 at 30 gives 18.00.

Accepted Answer

SELECT p.product_id,
       COALESCE(
           ROUND(SUM(p.price::numeric * u.units) / SUM(u.units), 2),
           0::numeric
       ) AS average_price
FROM prices AS p
LEFT JOIN unitssold AS u
  ON u.product_id = p.product_id
 AND u.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY p.product_id;

The date condition assigns each sale its applicable price. Nonoverlapping intervals prevent counting a sale twice. Sum revenue and units before dividing to weight prices by quantity; the numeric cast also avoids integer multiplication overflow. The left join retains products without sales, whose NULL aggregate result becomes zero through COALESCE.

Question 38

Original bridge S04 — Every student-subject pair.

Write a PostgreSQL query using CROSS JOIN for:

students(student_id integer PRIMARY KEY, student_name text NOT NULL)
subjects(subject_name text PRIMARY KEY)

Return every possible student/subject combination, with columns student_id, student_name, subject_name. Sort by student_id ascending, then subject_name ascending. Names of different students may match; their IDs distinguish them. Key columns are non-NULL. If either table is empty, return no rows.

For example, students (14, 'Nora') and (21, 'Tariq') with subjects Art and Biology produce four rows: each student paired once with each subject.

Accepted Answer

SELECT s.student_id, s.student_name, sub.subject_name
FROM students AS s
CROSS JOIN subjects AS sub
ORDER BY s.student_id ASC, sub.subject_name ASC;

CROSS JOIN pairs each row on the left with every row on the right, without a matching condition. With m students and n subjects, the result contains m × n rows. The primary keys make each student/subject pair unique.

Question 39

LeetCode #1280 — Students and Examinations

Write a PostgreSQL query for:

students(student_id integer PRIMARY KEY, student_name text NOT NULL)
subjects(subject_name text PRIMARY KEY)
examinations(student_id integer NOT NULL, subject_name text NOT NULL)

Each examination row records one attendance and references an existing student and subject. The table has no key; repeated rows represent repeated attendances. Every student studies every listed subject. Key columns are non-NULL; student names need not be unique.

Return student_id, student_name, subject_name, attended_exams for every student/subject pair, including pairs with zero attendances. Count all examination rows for the pair. Sort by student_id ascending, then subject_name ascending. If either students or subjects is empty, return no rows.

Accepted Answer

SELECT s.student_id, s.student_name, sub.subject_name,
       COUNT(e.student_id) AS attended_exams
FROM students AS s
CROSS JOIN subjects AS sub
LEFT JOIN examinations AS e
  ON e.student_id = s.student_id
 AND e.subject_name = sub.subject_name
GROUP BY s.student_id, s.student_name, sub.subject_name
ORDER BY s.student_id ASC, sub.subject_name ASC;

Start with every required pair, then attach its examination rows. Repeated attendances remain separate matches. COUNT(e.student_id) counts only actual matches; COUNT(*) would count the placeholder row for a pair with no examinations and incorrectly return one.

Question 40

LeetCode #1934 — Confirmation Rate

Write a PostgreSQL query for:

signups(user_id integer PRIMARY KEY, time_stamp timestamp NOT NULL)
confirmations(user_id integer, time_stamp timestamp, action text NOT NULL,
              PRIMARY KEY (user_id, time_stamp))

confirmations.user_id references signups.user_id; key columns are non-NULL. Each confirmation row is one requested message, with action equal to confirmed or timeout.

Return user_id, confirmation_rate for every signed-up user. The rate is confirmed messages divided by all requested messages, rounded to two decimal places. Users without requests need rate 0. The result is a fraction from 0 to 1, not a percentage. Any row order is acceptable. For example, one confirmed message and three timeouts give 0.25.

Accepted Answer

SELECT s.user_id,
       ROUND(
           AVG(CASE WHEN c.action = 'confirmed' THEN 1::numeric ELSE 0 END),
           2
       ) AS confirmation_rate
FROM signups AS s
LEFT JOIN confirmations AS c ON c.user_id = s.user_id
GROUP BY s.user_id;

For a user with requests, the numeric 1/0 average is confirmed requests divided by all requests. The left join also produces one placeholder row for a user without requests; its missing action follows ELSE 0, giving that user the required zero rate. Round the final average.

Question 41

LeetCode #1789 — Primary Department for Each Employee

Write a PostgreSQL query that chooses one department per employee: use the department marked 'Y' when the employee has several departments, or their only department otherwise.

Schema: Employee(employee_id int, department_id int, primary_flag text), primary key (employee_id, department_id). All columns are non-NULL. The flag is 'Y' or 'N'; each employee with several departments has exactly one 'Y', and an employee with one department has 'N'.

Return employee_id, department_id. Any row order is acceptable.

Accepted Answer

WITH department_counts AS (
    SELECT employee_id, COUNT(*) AS department_count
    FROM Employee
    GROUP BY employee_id
)
SELECT e.employee_id, e.department_id
FROM Employee AS e
JOIN department_counts AS c
    ON c.employee_id = e.employee_id
WHERE e.primary_flag = 'Y'
   OR c.department_count = 1;

The grouped count identifies employees with just one department. Those rows qualify regardless of their 'N' flag; employees with multiple departments qualify only through their 'Y' row.

Question 42

LeetCode #1045 — Customers Who Bought All Products

Write a PostgreSQL query for customers represented in Customer who have bought every product listed in Product. Repeated purchases must not increase coverage.

Schemas: Customer(customer_id int NOT NULL, product_key int NULL) may contain duplicate rows; non-NULL product keys reference Product.product_key. Product(product_key int PRIMARY KEY) contains the complete product list. A NULL product key contributes no purchase. If the product list is empty, every customer ID present in Customer qualifies.

Return one customer_id row per qualifying customer, in any order.

Accepted Answer

SELECT customer_id
FROM Customer
GROUP BY customer_id
HAVING COUNT(DISTINCT product_key) = (
    SELECT COUNT(*)
    FROM Product
);

The foreign key keeps purchased products within the catalog, so matching the two counts proves full coverage. DISTINCT prevents repeat purchases from inflating the count; COUNT ignores NULL product keys. With an empty catalog, a represented customer with zero known purchases still qualifies.

Question 43

LeetCode #1070 — Product Sales Analysis III

Write a PostgreSQL query that returns every sale from each product's earliest recorded sales year. Keep separate sales from that year as separate rows, even if their displayed values match.

Schema: Sales(sale_id int, product_id int, year int, quantity int, price int), primary key (sale_id, year). All columns are non-NULL; price is the price per unit. A product may have several sales in one year.

Return product_id, first_year, quantity, price, where first_year is that product's earliest year. Any row order is acceptable.

Accepted Answer

WITH first_years AS (
    SELECT product_id, MIN(year) AS first_year
    FROM Sales
    GROUP BY product_id
)
SELECT s.product_id, f.first_year, s.quantity, s.price
FROM Sales AS s
JOIN first_years AS f
    ON f.product_id = s.product_id
   AND f.first_year = s.year;

Find one earliest year per product, then join back on both the product and year. The join retrieves every sale in that year without summing quantities or removing duplicate-looking output rows.

Question 44

LeetCode #602 — Friend Requests II: Who Has the Most Friends

Each accepted request establishes an undirected friendship. Write a PostgreSQL query for the person with the largest number of friends.

Schema: RequestAccepted(requester_id int, accepter_id int, accept_date date), primary key (requester_id, accepter_id). All columns are non-NULL. There are no self-friendships, and each unordered pair appears once, in either direction. The input is nonempty and exactly one person has the largest friend count.

Return id, num: the person's ID and number of friends.

Accepted Answer

WITH friendship_endpoints AS (
    SELECT requester_id AS id
    FROM RequestAccepted
    UNION ALL
    SELECT accepter_id AS id
    FROM RequestAccepted
), friend_counts AS (
    SELECT id, COUNT(*) AS num
    FROM friendship_endpoints
    GROUP BY id
)
SELECT id, num
FROM friend_counts
WHERE num = (SELECT MAX(num) FROM friend_counts);

Each friendship contributes once to each participant. UNION ALL retains those contributions, and grouping counts them per person. The final comparison selects the guaranteed unique maximum.

Question 45

LeetCode #1341 — Movie Rating

Write a PostgreSQL query that returns two winners: first, the user who rated the most movies across all dates; second, the movie with the highest average rating during February 2020. Break user ties by the smallest name and movie ties by the smallest title, using PostgreSQL's "C" collation for text comparisons.

Schemas: Users(user_id int PRIMARY KEY, name text UNIQUE); Movies(movie_id int PRIMARY KEY, title text UNIQUE); MovieRating(movie_id int, user_id int, rating int, created_at date), primary key (movie_id, user_id), with foreign keys to the other tables. All columns are non-NULL. At least one February 2020 rating exists.

Return a single column results with the user name first and movie title second. Keep both rows even if their text matches.

Accepted Answer

WITH user_winner AS (
    SELECT u.name AS results
    FROM Users AS u
    JOIN MovieRating AS r ON r.user_id = u.user_id
    GROUP BY u.user_id, u.name
    ORDER BY COUNT(*) DESC, u.name COLLATE "C" ASC
    LIMIT 1
), movie_winner AS (
    SELECT m.title AS results
    FROM Movies AS m
    JOIN MovieRating AS r ON r.movie_id = m.movie_id
    WHERE r.created_at >= DATE '2020-02-01'
      AND r.created_at < DATE '2020-03-01'
    GROUP BY m.movie_id, m.title
    ORDER BY AVG(r.rating) DESC, m.title COLLATE "C" ASC
    LIMIT 1
), winners AS (
    SELECT 1 AS output_position, results FROM user_winner
    UNION ALL
    SELECT 2 AS output_position, results FROM movie_winner
)
SELECT results
FROM winners
ORDER BY output_position;

Each CTE sorts its own candidates and applies its own LIMIT 1. The movie date range includes leap day and excludes March. UNION ALL keeps two equal text values, while the outer ORDER BY guarantees the user row appears first; union branch order alone does not guarantee that.

Question 46

LeetCode #196 — Delete Duplicate Emails

Write a PostgreSQL DELETE statement that leaves exactly one row per email in Person, keeping the row with the smallest ID. Modify the table itself.

Schema: Person(id int PRIMARY KEY, email text NOT NULL). Email values contain no uppercase letters; equal text values represent duplicate emails.

The remaining table has columns id, email; its row order is irrelevant. A SELECT-only answer does not satisfy this task.

Accepted Answer

DELETE FROM Person AS p
WHERE EXISTS (
    SELECT 1
    FROM Person AS earlier
    WHERE earlier.email = p.email
      AND earlier.id < p.id
);

DELETE removes each row for which the correlated subquery finds the same email at a smaller ID. The minimum-ID row has no smaller match and survives. This changes stored rows; it does not just hide duplicates in a query result.

Question 47

Original bridge S01 — Number observations chronologically

Use PostgreSQL ROW_NUMBER to assign consecutive numbers starting at 1 to observations in a single stream. Earlier timestamps come first; break timestamp ties with the smaller observation ID.

Schema: Observation(observation_id int PRIMARY KEY, observed_at timestamp NOT NULL, value numeric NOT NULL). IDs are unique but need not be consecutive. Several observations may share a timestamp.

Return observation_id, observed_at, value, row_number, ordered by observed_at, observation_id ascending.

Accepted Answer

SELECT
    observation_id,
    observed_at,
    value,
    ROW_NUMBER() OVER (
        ORDER BY observed_at, observation_id
    ) AS row_number
FROM Observation
ORDER BY observed_at, observation_id;

ROW_NUMBER gives each row a different number in the window's order. The unique ID resolves timestamp ties, so numbering is repeatable. The outer ORDER BY separately guarantees the displayed row order.

Question 48

LeetCode #178 — Rank Scores

Write a PostgreSQL query that ranks scores from highest to lowest, starting at 1. Equal scores share a rank, and the next different score receives the next consecutive rank. Keep a result row for every input row.

Schema: Scores(id int PRIMARY KEY, score numeric NOT NULL). Scores have two decimal places and may repeat.

Return score, rank, sorted by score descending. Equal-score rows have identical displayed values, so their internal order does not matter.

Accepted Answer

SELECT
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROM Scores
ORDER BY score DESC;

DENSE_RANK assigns one rank per distinct score without gaps. Keep only score in the window's ORDER BY: adding the unique ID there would separate tied scores. The query retains repeated rows because it does not use DISTINCT.

Question 49

LeetCode #1303 — Find the Team Size

Write a PostgreSQL query that shows each employee's team size beside their employee ID. Include the employee themselves in that size.

Schema: Employee(employee_id int PRIMARY KEY, team_id int NOT NULL). Each row is one employee assigned to exactly one team; several employees may share a team ID.

Return employee_id, team_size, one row per employee, in any order.

Accepted Answer

SELECT
    employee_id,
    COUNT(*) OVER (PARTITION BY team_id) AS team_size
FROM Employee;

PARTITION BY makes each team's rows the counting group. Using COUNT as a window function leaves every employee row in the output. With no window ORDER BY, the count covers the entire team rather than a running portion of it.

Question 50

LeetCode #176 — Second Highest Salary

Write a PostgreSQL query for the second highest distinct salary. Repeated salaries occupy one position; ignore NULL salaries.

Schema: Employee(id int PRIMARY KEY, salary int NULL).

Return exactly one row with the column SecondHighestSalary. If fewer than two distinct non-NULL salaries exist, including when the table is empty, return NULL in that row.

Accepted Answer

WITH ranked_salaries AS (
    SELECT
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
    FROM Employee
    WHERE salary IS NOT NULL
)
SELECT MAX(salary) AS "SecondHighestSalary"
FROM ranked_salaries
WHERE salary_rank = 2;

DENSE_RANK makes rank 2 the second distinct salary, even when several employees share the highest salary. The outer MAX collapses repeated rank-2 rows into one value and returns one NULL row when no rank-2 rows exist. Quotes preserve the requested output-column capitalization.

Question 51

LeetCode #184 — Department Highest Salary

Write a PostgreSQL query that returns every employee tied for their department's highest salary. Departments without employees contribute no rows.

Schemas: Employee(id int PRIMARY KEY, name text NOT NULL, salary int NOT NULL, departmentId int NOT NULL) with departmentId referencing Department.id; Department(id int PRIMARY KEY, name text NOT NULL). Names need not be unique. Use unquoted input identifiers; PostgreSQL folds departmentId to departmentid.

Return Department, Employee, Salary: department name, employee name, and salary, with those exact column labels. Any row order is acceptable; preserve separate employees even when their displayed values match.

Accepted Answer

WITH ranked_employees AS (
    SELECT
        id,
        name,
        salary,
        departmentid,
        DENSE_RANK() OVER (
            PARTITION BY departmentid
            ORDER BY salary DESC
        ) AS salary_rank
    FROM Employee
)
SELECT
    d.name AS "Department",
    e.name AS "Employee",
    e.salary AS "Salary"
FROM ranked_employees AS e
JOIN Department AS d ON d.id = e.departmentid
WHERE e.salary_rank = 1;

Rank salaries independently within each department ID, then filter rank 1 in the outer query. Window results cannot be filtered by WHERE at the same query level. Dense ranking preserves all highest-salary ties; partitioning by ID also keeps departments with the same name separate.

Question 52

LeetCode #1174: Immediate Food Delivery II

Schema: Delivery(delivery_id integer PK, customer_id integer, order_date date, customer_pref_delivery_date date). All columns are NOT NULL. The table is nonempty, and each customer has one uniquely earliest order. Preferred delivery dates are on or after order dates.

Using PostgreSQL, consider only each customer’s first order. What percentage requested delivery on the order date? Return one column, immediate_percentage, rounded to two decimal places.

Accepted Answer

WITH ordered_deliveries AS (
    SELECT customer_id, order_date, customer_pref_delivery_date,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY order_date, delivery_id
           ) AS order_position
    FROM Delivery
)
SELECT ROUND(
           100 * AVG(CASE
               WHEN order_date = customer_pref_delivery_date THEN 1
               ELSE 0
           END),
           2
       ) AS immediate_percentage
FROM ordered_deliveries
WHERE order_position = 1;

ROW_NUMBER isolates one first order per customer. Filter those rows before averaging the 1/0 indicator, so every customer contributes once. PostgreSQL AVG over integers returns a fractional numeric result; multiply by 100 for a percentage.

Question 53

LeetCode #550: Game Play Analysis IV

Schema: Activity(player_id integer, device_id integer, event_date date, games_played integer), PK (player_id, event_date). All columns are NOT NULL; the table is nonempty. Every row represents a login, including rows with zero games.

Using PostgreSQL, find the fraction of players who also logged in on the calendar day immediately after their first login. Return one column, fraction, rounded to two decimal places. A return after a longer gap does not qualify.

Accepted Answer

WITH first_logins AS (
    SELECT player_id, MIN(event_date) AS first_date
    FROM Activity
    GROUP BY player_id
)
SELECT ROUND(
           AVG(CASE WHEN EXISTS (
               SELECT 1
               FROM Activity AS next_day
               WHERE next_day.player_id = first_logins.player_id
                 AND next_day.event_date = first_logins.first_date + 1
           ) THEN 1 ELSE 0 END),
           2
       ) AS fraction
FROM first_logins;

Group first to give each player one denominator row. EXISTS checks the next calendar date without counting later returns. Adding 1 to a PostgreSQL date advances it by one day; AVG of the indicator computes the fraction.

Question 54

LeetCode #1164: Product Price at a Given Date

Schema: Products(product_id integer, new_price integer, change_date date), PK (product_id, change_date). All columns are NOT NULL. Every row sets a product’s price from that date onward. The products to report are exactly the distinct product IDs in this table; each starts at price 10 before its first change.

Using PostgreSQL, return product_id, price as of 2019-08-16, including changes on that date. Any row order is acceptable.

Accepted Answer

WITH product_ids AS (
    SELECT DISTINCT product_id
    FROM Products
), eligible_changes AS (
    SELECT product_id, new_price,
           ROW_NUMBER() OVER (
               PARTITION BY product_id
               ORDER BY change_date DESC
           ) AS change_position
    FROM Products
    WHERE change_date <= DATE '2019-08-16'
)
SELECT product_ids.product_id,
       COALESCE(eligible_changes.new_price, 10) AS price
FROM product_ids
LEFT JOIN eligible_changes
    ON eligible_changes.product_id = product_ids.product_id
   AND eligible_changes.change_position = 1;

Filter to eligible dates before ranking newest first. The complete product list preserves products whose changes are all later; the left join and COALESCE assign those products the initial price of 10.

Question 55

LeetCode #585: Investments in 2016

Schema: Insurance(pid integer PK, tiv_2015 double precision, tiv_2016 double precision, lat double precision, lon double precision). All columns are NOT NULL and floating values are finite. Investment and coordinate comparisons use stored-value equality; a location is the complete (lat, lon) pair.

Using PostgreSQL, sum tiv_2016 for policies whose tiv_2015 matches another policy, but whose location matches no other policy. Return one column named tiv_2016, rounded to two decimals; return NULL if no policy qualifies.

Accepted Answer

WITH policy_counts AS (
    SELECT tiv_2016,
           COUNT(*) OVER (PARTITION BY tiv_2015) AS prior_value_count,
           COUNT(*) OVER (PARTITION BY lat, lon) AS location_count
    FROM Insurance
)
SELECT ROUND(SUM(tiv_2016)::numeric, 2) AS tiv_2016
FROM policy_counts
WHERE prior_value_count > 1
  AND location_count = 1;

Compute both counts over all policies before filtering: they describe independent groups. A coordinate pair must occur once even if other policies at that location would fail the investment condition. Cast the floating sum to numeric for PostgreSQL’s two-argument ROUND; an empty SUM stays NULL.

Question 56

Original bridge S02 — Show each observation’s previous value.

Schema: Observation(observation_id integer PK, observed_at timestamp, value numeric). All columns are NOT NULL. This is one observation stream. Order it by observed_at, then observation_id to resolve equal timestamps; time gaps are allowed.

Using PostgreSQL LAG, return observation_id, observed_at, value, previous_value for every row in that order. previous_value means the value from the immediately preceding observed row, regardless of elapsed time. Use NULL for the first row.

Accepted Answer

SELECT observation_id, observed_at, value,
       LAG(value) OVER (
           ORDER BY observed_at, observation_id
       ) AS previous_value
FROM Observation
ORDER BY observed_at, observation_id;

LAG(value) reads one row back in the window’s order and returns NULL when no preceding row exists. The ID makes timestamp ties deterministic. The final ORDER BY also puts the displayed rows in that order.

Question 57

LeetCode #180: Consecutive Numbers

Schema: Logs(id integer PK, num text NOT NULL). IDs establish ascending row order. Consecutive means adjacent rows in that order, even if IDs have gaps; compare the stored text values exactly.

Using PostgreSQL, return each num that occurs in at least three consecutive ordered rows. Return a single column named ConsecutiveNums, with each qualifying value once. Any row order is acceptable.

Accepted Answer

WITH preceding_values AS (
    SELECT num,
           LAG(num, 1) OVER (ORDER BY id) AS previous_num,
           LAG(num, 2) OVER (ORDER BY id) AS earlier_num
    FROM Logs
)
SELECT DISTINCT num AS "ConsecutiveNums"
FROM preceding_values
WHERE num = previous_num
  AND num = earlier_num;

At the third row of any qualifying run, both preceding values equal the current value. The offset of 2 reads two ordered rows back; it does not subtract from the ID. DISTINCT removes repeated detections from longer runs or separate runs of the same value.

Question 58

LeetCode #626: Exchange Seats

Schema: Seat(id integer PK, student text NOT NULL). IDs are exactly 1..n, with no gaps. Student names need not be unique.

Using PostgreSQL, exchange the students occupying seats 1 and 2, seats 3 and 4, and so on. If the row count is odd, keep the final student in the final seat. Return id, student in ascending id order. Produce a result set without updating the table.

Accepted Answer

SELECT current_seat.id,
       CASE
           WHEN partner.id IS NULL THEN current_seat.student
           ELSE partner.student
       END AS student
FROM Seat AS current_seat
LEFT JOIN Seat AS partner
    ON partner.id = CASE
        WHEN current_seat.id % 2 = 1 THEN current_seat.id + 1
        ELSE current_seat.id - 1
    END
ORDER BY current_seat.id;

The CASE expression maps each seat to its partner: odd IDs look forward, even IDs look back. Only a final unpaired odd seat lacks a match. The left join keeps that row, and the output CASE retains its student.

Question 59

LeetCode #1308: Running Total for Different Genders

Schema: Scores(player_name text, gender text, day date, score_points integer). All columns are NOT NULL; (gender, day) is unique. A row records points earned on that date for its gender; dates may have gaps.

Using PostgreSQL, report gender, day, total, where total includes that gender’s points on the current date and all earlier recorded dates. Return rows ordered by gender, then day, both ascending.

Accepted Answer

SELECT gender, day,
       SUM(score_points) OVER (
           PARTITION BY gender
           ORDER BY day
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS total
FROM Scores
ORDER BY gender, day;

PARTITION BY starts a separate total for each gender. The explicit ROWS frame includes every preceding row and the current row. The unique gender/date pair gives each partition a deterministic date order; missing dates do not interrupt the total.

Question 60

LeetCode #1204: Last Person to Fit in the Bus

Schema: Queue(person_id integer PK, person_name text, weight integer, turn integer UNIQUE). All columns are NOT NULL; weights are positive kilograms. The nonempty queue has turns 1..n, and the first person weighs at most 1000 kg.

People board in ascending turn, with no skipping. Boarding stops before the first person who would put total weight above 1000 kg; exactly 1000 is allowed. Using PostgreSQL, return one column, person_name, for the last person who boards.

Accepted Answer

WITH boarding_weights AS (
    SELECT person_name, turn,
           SUM(weight) OVER (
               ORDER BY turn
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
           ) AS boarded_weight
    FROM Queue
)
SELECT person_name
FROM boarding_weights
WHERE boarded_weight <= 1000
ORDER BY turn DESC
LIMIT 1;

The running sum includes each person’s own weight. Positive weights make it increase, so qualifying rows form the initial boarding group. Sort that group by descending turn and take its last boarder; <= includes an exact capacity match.

Question 61

Original bridge S03 — Three-observation moving average.

Schema: Observation(observation_id integer PK, observed_at timestamp, value numeric). All columns are NOT NULL. This is one stream, ordered by observed_at, then observation_id to resolve equal timestamps; time and ID gaps are allowed.

Using PostgreSQL and a row-based window frame, return observation_id, observed_at, value, moving_average in that stream order. Average the current value and up to two preceding observed values. Keep the first two rows with their partial windows: one value for the first row, two for the second. Do not round the average.

Accepted Answer

SELECT observation_id, observed_at, value,
       AVG(value) OVER (
           ORDER BY observed_at, observation_id
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_average
FROM Observation
ORDER BY observed_at, observation_id;

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW limits the frame to at most three observed rows, independent of time or ID gaps. PostgreSQL shortens the frame automatically at the start of the stream; AVG uses the rows that exist.

Question 62

LeetCode #1321: Restaurant Growth

Schema: Customer(customer_id integer, name text, visited_on date, amount integer), PK (customer_id, visited_on). All columns are NOT NULL. Each row is one customer’s total spending that day. There is at least one row on every calendar day between the earliest and latest recorded dates.

Using PostgreSQL, return visited_on, amount, average_amount for complete seven-day windows ending on each date. amount is total spending over that date and the prior six days; average_amount is the average daily total, rounded to two decimals. Omit incomplete windows and order by visited_on ascending.

Accepted Answer

WITH daily_spending AS (
    SELECT visited_on, SUM(amount) AS daily_amount
    FROM Customer
    GROUP BY visited_on
), rolling_spending AS (
    SELECT visited_on,
           SUM(daily_amount) OVER (
               ORDER BY visited_on
               ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
           ) AS amount,
           AVG(daily_amount) OVER (
               ORDER BY visited_on
               ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
           ) AS average_amount,
           COUNT(*) OVER (
               ORDER BY visited_on
               ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
           ) AS day_count
    FROM daily_spending
)
SELECT visited_on, amount,
       ROUND(average_amount, 2) AS average_amount
FROM rolling_spending
WHERE day_count = 7
ORDER BY visited_on;

Aggregate visits into one total per day before applying the frame. Guaranteed daily activity makes seven daily rows equal seven calendar days. COUNT identifies complete frames; filter them outside the window query and round only the final daily average.

Question 63

LeetCode #185: Department Top Three Salaries

Schemas: Employee(id integer PK, name text, salary integer, departmentId integer FK → Department.id); Department(id integer PK, name text). All columns are NOT NULL. Employee and department names may repeat; each employee belongs to one existing department.

Using PostgreSQL, return Department, Employee, Salary for every employee whose salary is among their department’s three highest distinct salaries. Include all employees tied at a qualifying salary; include every employee if their department has fewer than three distinct salaries. Any row order is acceptable.

Accepted Answer

WITH salary_levels AS (
    SELECT id, name, salary, departmentId,
           DENSE_RANK() OVER (
               PARTITION BY departmentId
               ORDER BY salary DESC
           ) AS salary_level
    FROM Employee
)
SELECT Department.name AS "Department",
       salary_levels.name AS "Employee",
       salary_levels.salary AS "Salary"
FROM salary_levels
JOIN Department ON Department.id = salary_levels.departmentId
WHERE salary_levels.salary_level <= 3;

DENSE_RANK gives equal salaries the same level without gaps, so levels 1–3 represent three distinct salary values and preserve all ties. Partition by department ID to keep departments separate even when names match. Filter the computed level in the outer query.

इस डेक के बारे में

इस डेक के कार्ड

कार्ड 1

Recyclable and Low Fat Products

कार्ड 2

Big Countries

कार्ड 3

Find Customer Referee

कार्ड 4

Article Views I

कार्ड 5

Invalid Tweets

कार्ड 6

Not Boring Movies

कार्ड 7

Triangle Judgement

कार्ड 8

Find Followers Count

कार्ड 9

Number of Unique Subjects Taught by Each Teacher

कार्ड 10

Classes More Than 5 Students

कार्ड 11

Game Play Analysis I

कार्ड 12

Find Total Time Spent by Each Employee

कार्ड 13

User Activity for the Past 30 Days I

कार्ड 14

Biggest Single Number

कार्ड 15

Fix Names in a Table

कार्ड 16

Patients With a Condition

कार्ड 17

Find Users With Valid E-Mails

कार्ड 18

Group Sold Products By The Date

कार्ड 19

कार्ड 20

कार्ड 21

कार्ड 22

कार्ड 23

कार्ड 24

कार्ड 25

कार्ड 26

कार्ड 27

कार्ड 28

कार्ड 29

कार्ड 30

कार्ड 31

कार्ड 32

कार्ड 33

कार्ड 34

कार्ड 35

कार्ड 36

कार्ड 37

कार्ड 38

कार्ड 39

कार्ड 40

कार्ड 41

कार्ड 42

कार्ड 43

कार्ड 44

कार्ड 45

कार्ड 46

कार्ड 47

कार्ड 48

कार्ड 49

कार्ड 50

कार्ड 51

कार्ड 52

कार्ड 53

कार्ड 54

कार्ड 55

कार्ड 56

कार्ड 57

कार्ड 58

कार्ड 59

कार्ड 60