SQL 50

tags: postgres, 02-lambdas-and-streams

1757. Recyclable and Low Fat Products Write a solution to find the ids of products that are both low fat and recyclable.

Input: products table
product_id	low_fats	recyclable
0	Y	N
1	Y	Y
2	N	Y
3	Y	Y
4	N	N

Output
product_id
1
3

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

products.stream()
	.filter(p -> p.low_fats == 'Y' && p.recyclable == 'Y')
	.map(p -> p.product_id)
	.collect(Collectors.toList());

---

584. Find Customer Referee Find the names of the customer that are either:

1. referred by any customer with id != 2.
2. not referred by any customer.

Input: customer table
id	name	referee_id
1	Will	null
2	Jane	null
3	Alex	2
4	Bill	null
5	Zack	1
6	Mark	2

Output
name
Will
Jane
Bill
Zack

SELECT name FROM customer
WHERE referee_id != 2 OR referee_id IS NULL;

customer.stream()
	.filter(c -> c.referee_id == null || c.referee_id != 2)
	.map(c -> c.name)
	.collect(Collectors.toList());

---

595. Big Countries Write a solution to find the name, population, and area of the big countries. A country is big if:

• it has an area of at least three million (i.e., 3000000 km2), or
• it has a population of at least twenty-five million (i.e., 25000000).

Input: World table
name	continent	area	population	gdp
Afghanistan	Asia	652230	25500100	20343000000
Albania	Europe	28748	2831741	12960000000
Algeria	Africa	2381741	37100000	188681000000
Andorra	Europe	468	78115	3712000000
Angola	Africa	1246700	20609294	100990000000

Output
name	population	area
Afghanistan	25500100	652230
Algeria	37100000	2381741

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

record WorldInfo(String name, long population, long area) {}
 
world.stream()
	.filter(w -> w.area >= 3000000 || w.population >= 25000000)
	.map(w -> new WorldInfo(w.name, w.population, w.area))
	.collect(Collectors.toList());

---

1148. Article Views I Write a solution to find all the authors that viewed at least one of their own articles. Return the result table sorted by id in ascending order.

Input: Views table
article_id	author_id	viewer_id	view_date
1	3	5	2019-08-01
1	3	6	2019-08-02
2	7	7	2019-08-01
2	7	6	2019-08-02
4	7	1	2019-07-22
3	4	4	2019-07-21
3	4	4	2019-07-21

Output
id
4
7

SELECT author_id as id FROM views
WHERE author_id = viewer_id
GROUP BY author_id
ORDER BY author_id
 
SELECT distinct author_id as id FROM views
WHERE aauthor_id = viewer_id
ORDER BY author_id

views.stream()
	.filter(v -> v.author_id == v.viewer_id)
	.collect(Collectors.groupingBy(v -> v.author_id))
	.keySet()
	.stream()
	.sorted()
	.collect(Collectors.toList());
	
views.stream()
	.filter(v -> v.author_id == v.viewer_id)
	.map(v -> v.author_id)
	.distinct()
	.sorted()
	.collect(Collectors.toList());

---

1683. Invalid Tweets Write a solution to find the IDs of the invalid tweets. The tweet is invalid if the number of characters used in the content of the tweet is strictly greater than 15.

Input: Tweets table
tweet_id	content
1	Let us Code
2	More than fifteen chars are here!

Output
tweet_id
2

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

tweets.stream()
	.filter(t -> t.length() > 15)
	.map(t -> t.tweet_id)
	.collect(Collectors.toList());

---

1378. Replace Employee ID With The Unique Identifier Write a solution to show the unique ID of each user, If a user does not have a unique ID replace just show null.

Input: Employees table
id	name
1	Alice
7	Bob
11	Meir
90	Winston
3	Jonathan

Input: EmployeeUNI table
id	unique_id
3	1
11	2
90	3

Output
unique_id	name
null	Alice
null	Bob
2	Meir
3	Winston
1	Jonathan

SELECT unique_id, name FROM employees e
LEFT JOIN employeeuni eu ON e.id = eu.id;

record EmployeeJoin(Integer uniqueId, String name) {}
 
Map<Integer, Integer> euMap = employeeUni.stream()
    .collect(Collectors.toMap(eu -> eu.id, eu.unique_id);
 
List<EmployeeJoin> result = employees.stream()
    .map(e -> new EmployeeJoin(euMap.get(e.id), e.name)
    .toList();

---

1068. Product Sales Analysis I Write a solution to report the product_name, year, and price for each sale_id in the Sales table.

Input: Sales table
sale_id	product_id	year	quantity	price
1	100	2008	10	5000
2	100	2009	12	5000
7	200	2011	15	9000

Input: Product table
product_id	product_name
100	Nokia
200	Apple
300	Samsung

Output
product_name	year	price
Nokia	2008	5000
Nokia	2009	5000
Apple	2011	9000

SELECT product_name, year, price FROM sales s
JOIN product p ON p.product_id = s.product_id

---

1581. Customer Who Visited but Did Not Make Any Transactions Write a solution to find the IDs of the users who visited without making any transactions and the number of times they made these types of visits.

Input: Visits table
visit_id	customer_id
1	23
2	9
4	30
5	54
6	96
7	54
8	54

Input: Transactions table
transaction_id	visit_id	amount
2	5	310
3	5	300
9	5	200
12	1	910
13	2	970

Output
customer_id	count_no_trans
54	2
30	1
96	1

SELECT customer_id, COUNT(*) AS count_no_trans FROM visits v
WHERE NOT EXISTS(
    SELECT t.visit_id FROM transactions t
    WHERE t.visit_id = v.visit_id
)
GROUP BY customer_id;
 
SELECT customer_id, count(*) AS count_no_trans FROM visits v
LEFT JOIN transactions t ON v.visit_id = t.visit_id
WHERE transaction_id IS NULL
GROUP BY customer_id;

---

197. Rising Temperature Write a solution to find all dates’ id with higher temperatures compared to its previous dates (yesterday).

Input: Weather table
id	recordDate	temperature
1	2015-01-01	10
2	2015-01-02	25
3	2015-01-03	20
4	2015-01-04	30

Output
id
2
4

SELECT w1.id FROM weather w1
JOIN weather w2 on w1.recorddate = w2.recorddate + INTERVAL '1 day'
WHERE w1.temperature > w2.temperature;
 
SELECT w1.id FROM weather w1
CROSS JOIN weather w2
WHERE w1.recorddate - w2.recorddate = 1
AND w1.temperature > w2.temperature

---

1661. Average Time of Process per Machine Write a solution to find the average time each machine takes to complete a process. Round the answer to 3 decimal places.

Input: Activity table
machine_id	process_id	activity_type	timestamp
0	0	start	0.712
0	0	end	1.520
0	1	start	3.140
0	1	end	4.120
1	0	start	0.550
1	0	end	1.550
1	1	start	0.430
1	1	end	1.420

Output
machine_id	processing_time
0	0.894
1	0.995

SELECT a1.machine_id, ROUND((SUM(a2.timestamp - a1.timestamp) / COUNT(*))::numeric, 3) AS processing_time
FROM activity a1
JOIN activity a2 ON a1.activity_type = 'start' AND a2.activity_type = 'end'
AND a1.machine_id = a2.machine_id AND a1.process_id = a2.process_id
GROUP BY a1.machine_id;
 
SELECT a1.machine_id, ROUND(AVG(a2.timestamp - a1.timestamp)::numeric, 3) AS processing_time
FROM activity a1
JOIN activity a2 ON a1.activity_type = 'start' AND a2.activity_type = 'end'
AND a1.machine_id = a2.machine_id AND a1.process_id = a2.process_id
GROUP BY a1.machine_id;

---

577. Employee Bonus Write a solution to report the name and bonus amount of each employee with a bonus less than 1000.

Input: Employee table
empId	name	supervisor	salary
3	Brad	null	4000
1	John	3	1000
2	Dan	3	2000
4	Thomas	3	4000

Input: Bonus table
empId	bonus
2	500
4	2000

Output
name	bonus
Brad	null
John	null
Dan	500

SELECT name, bonus FROM employee e
LEFT JOIN bonus b ON e.empid = b.empid
WHERE bonus IS NULL OR bonus < 1000;

---

1280. Students and Examinations Write a solution to find the number of times each student attended each exam. Return the result table ordered by student_id and subject_name.

Input: Students table
student_id	student_name
1	Alice
2	Bob
13	John
6	Alex

Input: Examinations table
student_id	subject_name
1	Math
1	Physics
1	Programming
2	Programming
1	Physics
1	Math
13	Math
13	Programming
13	Physics
2	Math
1	Math

Input: Subjects table
subject_name
Math
Physics
Programming

Output
student_id	student_name	subject_name	attended_exams
1	Alice	Math	3
1	Alice	Physics	2
1	Alice	Programming	1
2	Bob	Math	1
2	Bob	Physics	0
2	Bob	Programming	1
6	Alex	Math	0
6	Alex	Physics	0
6	Alex	Programming	0
13	John	Math	1
13	John	Physics	1
13	John	Programming	1

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

---

570. Managers with at Least 5 Direct Reports Write a solution to find managers with at least five direct reports.

Input: Employee table
id	name	department	managerId
101	John	A	null
102	Dan	A	101
103	James	A	101
104	Amy	A	101
105	Anne	A	101
106	Ron	B	101

Output
name
John

SELECT m.name FROM Employee m 
JOIN Employee e ON m.id = e.managerId 
GROUP BY m.id, m.name HAVING COUNT(1) >= 5;

---

1934. Confirmation Rate Write a solution to find the confirmation rate of each user. Round to two decimal places. The confirmation rate is the number of ‘confirmed’ messages divided by the total number of requested confirmation messages.

Input: Signups table
user_id	time_stamp
3	2020-03-21 10:16:13
7	2020-01-04 13:57:59
2	2020-07-29 23:09:44
6	2020-12-09 10:39:37

Input: Confirmations table
user_id	time_stamp	action
3	2021-01-06 03:30:46	timeout
3	2021-07-14 14:00:00	timeout
7	2021-06-12 11:57:29	confirmed
7	2021-06-13 12:58:28	confirmed
7	2021-06-14 13:59:27	confirmed
2	2021-01-22 00:00:00	confirmed
2	2021-02-28 23:59:59	timeout

Output
user_id	confirmation_rate
6	0.00
3	0.00
7	1.00
2	0.50

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

---

620. Not Boring Movies

Write a solution to report the movies with an odd-numbered ID and a description that is not “boring”. Return the result table ordered by rating in descending order.

Input: Cinema table
id	movie	description	rating
1	War	great 3D	8.9
2	Science	fiction	8.5
3	irish	boring	6.2
4	Ice song	Fantacy	8.6
5	House card	Interesting	9.1

Output
id	movie	description	rating
5	House card	Interesting	9.1
1	War	great 3D	8.9

SELECT * FROM cinema
WHERE description <> 'boring' AND id % 2 = 1
ORDER BY id DESC;

---

1251. Average Selling Price

Write a solution to find the average selling price for each product. Round to 2 decimal places. If a product does not have any sold units, its average price is 0.

Input: Prices table
product_id	start_date	end_date	price
1	2019-02-17	2019-02-28	5
1	2019-03-01	2019-03-22	20
2	2019-02-01	2019-02-20	15
2	2019-02-21	2019-03-31	30

Input: UnitsSold table
product_id	purchase_date	units
1	2019-02-25	100
1	2019-03-01	15
2	2019-02-10	200
2	2019-03-22	30

Output
product_id	average_price
1	6.96
2	16.96

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

---

1075. Project Employees I

Write a solution to report the average experience years of all the employees for each project, rounded to 2 digits.

Input: Project table
project_id	employee_id
1	1
1	2
1	3
2	1
2	4

Employee table
employee_id	name	experience_years
1	Khaled	3
2	Ali	2
3	John	1
4	Doe	2

Output
project_id	average_years
1	2.00
2	2.50

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

---

1633. Percentage of Users Attended a Contest

Write a solution to find the percentage of the users registered in each contest rounded to two decimals. Return the result table ordered by percentage in descending order. In case of a tie, order by contest_id in ascending order.

Input: Users table
user_id	user_name
6	Alice
2	Bob
7	Alex

Register table
contest_id	user_id
215	6
209	2
208	2
210	6
208	6
209	7
209	6
215	7
208	7
210	2
207	2
210	7

Output
contest_id	percentage
208	100.00
209	100.00
210	100.00
215	66.67
207	33.33

SELECT r.contest_id, 
ROUND(COUNT(DISTINCT r.user_id)::numeric * 100 / (SELECT COUNT(*) FROM Users), 2) AS percentage
FROM Register r 
GROUP BY r.contest_id 
ORDER BY percentage DESC, r.contest_id;

WITH total AS (SELECT count(1) AS c from users)
 
SELECT contest_id,
ROUND(count(distinct user_id)::numeric * 100 / total.c, 2) as percentage
FROM register
CROSS JOIN total
GROUP BY contest_id, c
ORDER BY percentage desc, contest_id;

---

1211. Queries Quality and Percentage

Write a solution to find each query_name, the quality and poor_query_percentage. Both quality and poor_query_percentage should be rounded to 2 decimal places. Quality is the average of the ratio between query rating and position. A query is poor if its rating is less than 3.

Input: Queries table
query_name	result	position	rating
Dog	Golden Retriever	1	5
Dog	German Shepherd	2	5
Dog	Mule	200	1
Cat	Shirazi	5	2
Cat	Siamese	3	3
Cat	Sphynx	7	4

Output
query_name	quality	poor_query_percentage
Dog	2.50	33.33
Cat	0.66	33.33

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

---

1193. Monthly Transactions I

1193. Monthly Transactions I

Write a solution to find for each month and country, the number of transactions and their total amount, the number of approved transactions and their total amount.

Input: Transactions table
id	country	state	amount	trans_date
121	US	approved	1000	2018-12-18
122	US	declined	2000	2018-12-19
123	US	approved	2000	2019-01-01
124	DE	approved	2000	2019-01-07

Output
month	country	trans_count	approved_count	trans_total_amount	approved_total_amount
2018-12	US	2	1	3000	1000
2019-01	US	1	1	2000	2000
2019-01	DE	1	1	2000	2000

SELECT TO_CHAR(trans_date, 'YYYY-MM') AS month, country, count(*) AS trans_count,
SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 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 month, country;

---

1174. Immediate Food Delivery II

1174. Immediate Food Delivery II

Write a solution to find the percentage of immediate orders in the first orders of all customers. Round to 2 decimal places. An order is immediate if the order date equals the customer preferred delivery date.

Input: Delivery table
delivery_id	customer_id	order_date	customer_pref_delivery_date
1	1	2019-08-01	2019-08-02
2	2	2019-08-02	2019-08-02
3	1	2019-08-11	2019-08-12
4	3	2019-08-24	2019-08-24
5	3	2019-08-21	2019-08-22
6	2	2019-08-11	2019-08-13
7	4	2019-08-09	2019-08-09

Output
immediate_percentage
50.00

SELECT ROUND(AVG(CASE WHEN order_date = customer_pref_delivery_date THEN 1 ELSE 0 END) * 100, 2) AS immediate_percentage
FROM delivery
WHERE (customer_id, order_date) IN (
    SELECT customer_id, MIN(order_date) AS first_order FROM delivery
    GROUP BY customer_id
)

WITH first_orders AS (
    SELECT customer_id, MIN(order_date) AS first_order_date FROM Delivery
    GROUP BY customer_id 
) 
 
SELECT ROUND(SUM(CASE WHEN d.order_date = d.customer_pref_delivery_date THEN 1 ELSE 0 END)::numeric * 100 / COUNT(*), 2) AS immediate_percentage 
FROM Delivery d 
JOIN first_orders f ON d.customer_id = f.customer_id  AND d.order_date = f.first_order_date;

---

550. Game Play Analysis IV

550. Game Play Analysis IV

Write a solution to report the fraction of players that logged in again on the day after the day they first logged in, rounded to 2 decimal places.

Input: Activity table
player_id	device_id	event_date	games_played
1	2	2016-03-01	5
1	2	2016-03-02	6
2	3	2017-06-25	1
3	1	2016-03-02	0
3	4	2018-07-03	5

Output
fraction
0.33

WITH first_login AS (
    SELECT player_id, MIN(event_date) AS first_login_date FROM activity
    GROUP BY player_id
)
 
SELECT ROUND(COUNT(a2.player_id)::NUMERIC / COUNT(a1.player_id), 2) AS fraction
FROM first_login a1
LEFT JOIN activity a2 ON a1.player_id = a2.player_id
AND a1.first_login_date = a2.event_date - INTERVAL '1 day';

---

2356. Number of Unique Subjects Taught by Each Teacher

2356. Number of Unique Subjects Taught by Each Teacher

Write a solution to calculate the number of unique subjects each teacher teaches in the university.

Input: Teacher table
teacher_id	subject_id	dept_id
1	2	3
1	2	4
1	3	3
2	1	1
2	2	1
2	3	1
2	4	1

Output
teacher_id	cnt
1	2
2	4

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

---

1141. User Activity for the Past 30 Days I

1141. User Activity for the Past 30 Days I

Write a solution to find the daily active user count for a period of 30 days ending 2019-07-27 inclusively. A user was active on some day if they made at least one activity on that day.

Input: Activity table
user_id	session_id	activity_date	activity_type
1	1	2019-07-20	open_session
1	1	2019-07-20	scroll_down
1	1	2019-07-20	end_session
2	4	2019-07-20	open_session
2	4	2019-07-21	send_message
2	4	2019-07-21	end_session
3	2	2019-07-21	open_session
3	2	2019-07-21	send_message
3	2	2019-07-21	end_session
4	3	2019-06-25	open_session
4	3	2019-06-25	end_session

Output
day	active_users
2019-07-20	2
2019-07-21	2

SELECT activity_date AS day, count(DISTINCT user_id) AS active_users FROM activity
WHERE activity_date BETWEEN '2019-07-27'::date - INTERVAL '29 DAYS' AND '2019-07-27'
GROUP BY activity_date;

---

1070. Product Sales Analysis III

1070. Product Sales Analysis III

Write a solution to select the product id, year, quantity, and price for the first year of every product sold.

Input: Sales table
sale_id	product_id	year	quantity	price
1	100	2008	10	5000
2	100	2009	12	5000
7	200	2011	15	9000

Product table
product_id	product_name
100	Nokia
200	Apple
300	Samsung

Output
product_id	first_year	quantity	price
100	2008	10	5000
200	2011	15	9000

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

---

596. Classes More Than 5 Students

596. Classes More Than 5 Students

Write a solution to find all the classes that have at least five students.

Input: Courses table
student	class
A	Math
B	English
C	Math
D	Biology
E	Math
F	Computer
G	Math
H	Math
I	Math

Output
class
Math

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

courses.stream()
	.collect(Collectors.groupingBy(course -> course.getClass(), Collectors.counting()))
	.entrySet().stream()
	.filter(entry -> entry.getValue() >= 5)
	.map(entry -> entry.getKey())
	.collect(Collectors.toList())

---

1729. Find Followers Count

1729. Find Followers Count

Write a solution that will, for each user, return the number of followers. Return the result table ordered by user_id in ascending order.

Input: Followers table
user_id	follower_id
0	1
1	0
2	0
2	1

Output
user_id	followers_count
0	1
1	1
2	2

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

---

619. Biggest Single Number

619. Biggest Single Number

Write a solution to report the largest single number. If there is no single number, report null. A single number is a number that appeared only once.

Input: MyNumbers table
num
8
8
3
3
1
4
5
6

Output
num
6

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

---

1045. Customers Who Bought All Products

1045. Customers Who Bought All Products

Write a solution to report the customer ids that bought all the products in the Product table.

Input: Customer table
customer_id	product_key
1	5
2	6
3	5
3	6
1	6

Product table
product_key
5
6

Output
customer_id
1
3

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

---

1731. The Number of Employees Which Report to Each Employee

1731. The Number of Employees Which Report to Each Employee

Write a solution to report the ids and names of all managers, the number of employees who report directly to them, and the average age of the reports rounded to the nearest integer. Return the result table ordered by employee_id.

Input: Employees table
employee_id	name	reports_to	age
9	Hercy	null	43
6	Alice	9	41
4	Bob	9	36
2	Winston	null	37

Output
employee_id	name	reports_count	average_age
9	Hercy	2	39

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

---

1789. Primary Department for Each Employee

1789. Primary Department for Each Employee

Write a solution to report all the employees with their primary department. For employees who belong to one department, report their only department.

Input: Employee table
employee_id	department_id	primary_flag
1	1	N
2	1	Y
2	2	N
3	3	N
4	2	N
4	3	Y
4	4	N

Output
employee_id	department_id
1	1
2	1
3	3
4	3

SELECT employee_id, department_id FROM employee
WHERE primary_flag = 'Y'
UNION
SELECT employee_id, department_id FROM employee
WHERE employee_id IN (
    SELECT employee_id FROM employee
    GROUP BY employee_id HAVING COUNT(*) = 1
);
 
 
SELECT employee_id, department_id FROM employee
WHERE primary_flag = 'Y'
OR employee_id IN (
    SELECT employee_id FROM employee
    GROUP BY employee_id HAVING COUNT(*) = 1
);

---

610. Triangle Judgement

610. Triangle Judgement

Write a solution to report for every three line segments whether they can form a triangle.

Input: Triangle table
x	y	z
13	15	30
10	20	15

Output
x	y	z	triangle
13	15	30	No
10	20	15	Yes

SELECT x, y, z,
CASE WHEN (x + y > z) AND (y + z > x) AND (z + x > y)
     THEN 'Yes'
     ELSE 'No'
END AS triangle FROM triangle

---

180. Consecutive Numbers

180. Consecutive Numbers

Write a solution to find all numbers that appear at least three times consecutively.

Input: Logs table
id	num
1	1
2	1
3	1
4	2
5	1
6	2
7	2

Output
ConsecutiveNums
1

SELECT DISTINCT l1.num AS consecutivenums FROM logs l1
JOIN logs l2 ON l1.id = l2.id - 1 AND l1.num = l2.num
JOIN logs l3 ON l2.id = l3.id - 1 AND l2.num = l3.num;

---

1164. Product Price at a Given Date

1164. Product Price at a Given Date

Write a solution to find the prices of all products on 2019-08-16. Assume the price of all products before any change is 10.

Input: Products table
product_id	new_price	change_date
1	20	2019-08-14
2	50	2019-08-14
1	30	2019-08-15
1	35	2019-08-16
2	65	2019-08-17
3	20	2019-08-18

Output
product_id	price
2	50
1	35
3	10

WITH latest_prices AS (
	SELECT product_id, new_price, change_date,
	ROW_NUMBER() OVER (
		PARTITION BY product_id 
		ORDER BY change_date DESC
	) AS rn 
	FROM products 
	WHERE change_date <= '2019-08-16' 
) 
 
SELECT product_id, new_price AS price FROM latest_prices 
WHERE rn = 1 
UNION 
SELECT DISTINCT product_id, 10 AS price FROM products 
WHERE product_id NOT IN (
	SELECT product_id FROM products WHERE change_date <= '2019-08-16'
);

WITH unique_products AS (
    SELECT product_id , MAX(change_date) AS date FROM products
    WHERE change_date <= '2019-08-16'
    GROUP BY product_id
),
 
unique_prices AS (
    SELECT p.product_id, new_price AS price FROM products p 
    JOIN unique_products u 
    ON p.product_id = u.product_id AND p.change_date = u.date
)
 
SELECT DISTINCT p.product_id, COALESCE(price, 10) AS price FROM products p
LEFT JOIN unique_prices u 
ON p.product_id = u.product_id;