8000 Upload 2-Basic-Joins · rajtuts/raj-leetcode-sql-50@afa9147 · GitHub
[go: up one dir, main page]
Skip to content

Commit afa9147

Browse files
ziaroqueziaroque
committed
Upload 2-Basic-Joins
1 parent e957237 commit afa9147

10 files changed

+506
-11
lines changed

‎2-Basic-Joins/1068_Product_Sales_Analysis I.sql

Lines changed: 54 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,54 @@
1+
-- Source: https://leetcode.com/problems/product-sales-analysis-i/description/?envType=study-plan-v2&envId=top-sql-50
2+
3+
-- Table: Sales
4+
5+
-- +-------------+-------+
6+
-- | Column Name | Type |
7+
-- +-------------+-------+
8+
-- | sale_id | int |
9+
-- | product_id | int |
10+
-- | year | int |
11+
-- | quantity | int |
12+
-- | price | int |
13+
-- +-------------+-------+
14+
-- (sale_id, year) is the primary key (combination of columns with unique values) of this table.
15+
-- product_id is a foreign key (reference column) to Product table.
16+
-- Each row of this table shows a sale on the product product_id in a certain year.
17+
-- Note that the price is per unit.
18+
19+
-- Table: Product
20+
21+
-- +--------------+---------+
22+
-- | Column Name | Type |
23+
-- +--------------+---------+
24+
-- | product_id | int |
25+
-- | product_name | varchar |
26+
-- +--------------+---------+
27+
-- product_id is the primary key (column with unique values) of this table.
28+
-- Each row of this table indicates the product name of each product.
29+
30+
-- Write a solution to report the product_name, year, and price for each sale_id in the Sales table.
31+
32+
-- Return the resulting table in any order.
33+
34+
------------------------------------------------------------------------------
35+
36+
-- SQL Schema
37+
38+
Create table If Not Exists Sales (sale_id int, product_id int, year int, quantity int, price int)
39+
Create table If Not Exists Product (product_id int, product_name varchar(10))
40+
Truncate table Sales
41+
insert into Sales (sale_id, product_id, year, quantity, price) values ('1', '100', '2008', '10', '5000')
42+
insert into Sales (sale_id, product_id, year, quantity, price) values ('2', '100', '2009', '12', '5000')
43+
insert into Sales (sale_id, product_id, year, quantity, price) values ('7', '200', '2011', '15', '9000')
44+
Truncate table Product
45+
insert into Product (product_id, product_name) values ('100', 'Nokia')
46+
insert into Product (product_id, product_name) values ('200', 'Apple')
47+
insert into Product (product_id, product_name) values ('300', 'Samsung')
48+
49+
-- MS SQL Server Code
50+
51+
SELECT p.product_name, s.year, s.price
52+
FROM Sales s
53+
JOIN Product p
54+
ON s.product_id = p.product_id

‎2-Basic-Joins/1280_Students_and_Examinations.sql

Lines changed: 77 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,77 @@
1+
-- Source: https://leetcode.com/problems/students-and-examinations/description/?envType=study-plan-v2&envId=top-sql-50
2+
3+
-- Table: Students
4+
5+
-- +---------------+---------+
6+
-- | Column Name | Type |
7+
-- +---------------+---------+
8+
-- | student_id | int |
9+
-- | student_name | varchar |
10+
-- +---------------+---------+
11+
-- student_id is the primary key (column with unique values) for this table.
12+
-- Each row of this table contains the ID and the name of one student in the school.
13+
14+
-- Table: Subjects
15+
16+
-- +--------------+---------+
17+
-- | Column Name | Type |
18+
-- +--------------+---------+
19+
-- | subject_name | varchar |
20+
-- +--------------+---------+
21+
-- subject_name is the primary key (column with unique values) for this table.
22+
-- Each row of this table contains the name of one subject in the school.
23+
24+
-- Table: Examinations
25+
26+
-- +--------------+---------+
27+
-- | Column Name | Type |
28+
-- +--------------+---------+
29+
-- | student_id | int |
30+
-- | subject_name | varchar |
31+
-- +--------------+---------+
32+
-- There is no primary key (column with unique values) for this table. It may contain duplicates.
33+
-- Each student from the Students table takes every course from the Subjects table.
34+
-- Each row of this table indicates that a student with ID student_id attended the exam of subject_name.
35+
36+
-- Write a solution to find the number of times each student attended each exam.
37+
38+
-- Return the result table ordered by student_id and subject_name.
39+
40+
------------------------------------------------------------------------------
41+
42+
-- SQL Schema
43+
44+
Create table If Not Exists Students (student_id int, student_name varchar(20))
45+
Create table If Not Exists Subjects (subject_name varchar(20))
46+
Create table If Not Exists Examinations (student_id int, subject_name varchar(20))
47+
Truncate table Students
48+
insert into Students (student_id, student_name) values ('1', 'Alice')
49+
insert into Students (student_id, student_name) values ('2', 'Bob')
50+
insert into Students (student_id, student_name) values ('13', 'John')
51+
insert into Students (student_id, student_name) values ('6', 'Alex')
52+
Truncate table Subjects
53+
insert into Subjects (subject_name) values ('Math')
54+
insert into Subjects (subject_name) values (< 10000 span class="pl-s">'Physics')
55+
insert into Subjects (subject_name) values ('Programming')
56+
Truncate table Examinations
57+
insert into Examinations (student_id, subject_name) values ('1', 'Math')
58+
insert into Examinations (student_id, subject_name) values ('1', 'Physics')
59+
insert into Examinations (student_id, subject_name) values ('1', 'Programming')
60+
insert into Examinations (student_id, subject_name) values ('2', 'Programming')
61+
insert into Examinations (student_id, subject_name) values ('1', 'Physics')
62+
insert into Examinations (student_id, subject_name) values ('1', 'Math')
63+
insert into Examinations (student_id, subject_name) values ('13', 'Math')
64+
insert into Examinations (student_id, subject_name) values ('13', 'Programming')
65+
insert into Examinations (student_id, subject_name) values ('13', 'Physics')
66+
insert into Examinations (student_id, subject_name) values ('2', 'Math')
67+
insert into Examinations (student_id, subject_name) values ('1', 'Math')
68+
69+
-- MS SQL Server Code
70+
71+
SELECT s.student_id, s.student_name, u.subject_name, COUNT(e.subject_name) as 'attended_exams'
72+
FROM Students s
73+
CROSS JOIN Subjects u
74+
LEFT JOIN Examinations e
75+
ON s.student_id = e.student_id AND u.subject_name = e.subject_name
76+
GROUP BY s.student_id, s.student_name, u.subject_name
77+
ORDER BY s.student_id, u.subject_name

‎2-Basic-Joins/1378_Replace_Employee_ID_With_The_Unique_Identifier.sql

Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,52 @@
1+
-- Source: https://leetcode.com/problems/replace-employee-id-with-the-unique-identifier/description/?envType=study-plan-v2&envId=top-sql-50
2+
3+
-- Table: Employees
4+
5+
-- +---------------+---------+
6+
-- | Column Name | Type |
7+
-- +---------------+---------+
8+
-- | id | int |
9+
-- | name | varchar |
10+
-- +---------------+---------+
11+
-- id is the primary key (column with unique values) for this table.
12+
-- Each row of this table contains the id and the name of an employee in a company.
13+
14+
-- Table: EmployeeUNI
15+
16+
-- +---------------+---------+
17+
-- | Column Name | Type |
18+
-- +---------------+---------+
19+
-- | id | int |
20+
-- | unique_id | int |
21+
-- +---------------+---------+
22+
-- (id, unique_id) is the primary key (combination of columns with unique values) for this table.
23+
-- Each row of this table contains the id and the corresponding unique id of an employee in the company.
24+
25+
-- Write a solution to show the unique ID of each user, If a user does not have a unique ID replace just show null.
26+
27+
-- Return the result table in any order.
28+
29+
------------------------------------------------------------------------------
30+
31+
-- SQL Schema
32+
33+
Create table If Not Exists Employees (id int, name varchar(20))
34+
Create table If Not Exists EmployeeUNI (id int, unique_id int)
35+
Truncate table Employees
36+
insert into Employees (id, name) values ('1', 'Alice')
37+
insert into Employees (id, name) values ('7', 'Bob')
38+
insert into Employees (id, name) values ('11', 'Meir')
39+
insert into Employees (id, name) values ('90', 'Winston')
40+
insert into Employees (id, name) values ('3', 'Jonathan')
41+
Truncate table EmployeeUNI
42+
insert into EmployeeUNI (id, unique_id) values ('3', '1')
43+
insert into EmployeeUNI (id, unique_id) values ('11', '2')
44+
insert into EmployeeUNI (id, unique_id) values ('90', '3')
45+
46+
-- MS SQL Server Code
47+
48+
49+
SELECT eu.unique_id, e.name
50+
FROM Employees e
51+
LEFT JOIN EmployeeUNI eu
52+
ON e.id = eu.id

‎2-Basic-Joins/1581_Customer_Who_Visited_but_Did_Not_Make_Any_Transactions.sql

Lines changed: 58 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,58 @@
1+
-- Source: https://leetcode.com/problems/customer-who-visited-but-did-not-make-any-transactions/description/?envType=study-plan-v2&envId=top-sql-50
2+< 1241 div class="diff-text-inner">
3+
-- Table: Visits
4+
5+
-- +-------------+---------+
6+
-- | Column Name | Type |
7+
-- +-------------+---------+
8+
-- | visit_id | int |
9+
-- | customer_id | int |
10+
-- +-------------+---------+
11+
-- visit_id is the column with unique values for this table.
12+
-- This table contains information about the customers who visited the mall.
13+
14+
-- Table: Transactions
15+
16+
-- +----------------+---------+
17+
-- | Column Name | Type |
18+
-- +----------------+---------+
19+
-- | transaction_id | int |
20+
-- | visit_id | int |
21+
-- | amount | int |
22+
-- +----------------+---------+
23+
-- transaction_id is column with unique values for this table.
24+
-- This table contains information about the transactions made during the visit_id.
25+
26+
-- 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.
27+
28+
-- Return the result table sorted in any order.
29+
30+
------------------------------------------------------------------------------
31+
32+
-- SQL Schema
33+
34+
Create table If Not Exists Visits(visit_id int, customer_id int)
35+
Create table If Not Exists Transactions(transaction_id int, visit_id int, amount int)
36+
Truncate table Visits
37+
insert into Visits (visit_id, customer_id) values ('1', '23')
38+
insert into Visits (visit_id, customer_id) values ('2', '9')
39+
insert into Visits (visit_id, customer_id) values ('4', '30')
40+
insert into Visits (visit_id, customer_id) values ('5', '54')
41+
insert into Visits (visit_id, customer_id) values ('6', '96')
42+
insert into Visits (visit_id, customer_id) values ('7', '54')
43+
insert into Visits (visit_id, customer_id) values ('8', '54')
44+
Truncate table Transactions
45+
insert into Transactions (transaction_id, visit_id, amount) values ('2', '5', '310')
46+
insert into Transactions (transaction_id, visit_id, amount) values ('3', '5', '300')
47+
insert into Transactions (transaction_id, visit_id, amount) values ('9', '5', '200')
48+
insert into Transactions (transaction_id, visit_id, amount) values ('12', '1', '910')
49+
insert into Transactions (transaction_id, visit_id, amount) values ('13', '2', '970')
50+
51+
-- MS SQL Server Code
52+
53+
SELECT v.customer_id, COUNT(*) as 'count_no_trans'
54+
FROM Visits v
55+
LEFT JOIN Transactions t
56+
ON v.visit_id = t.visit_id
57+
WHERE t.transaction_id IS NULL
58+
GROUP BY v.customer_id

‎2-Basic-Joins/1661_Average_Time_of_Process_per_Machine.sql

Lines changed: 58 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,58 @@
1+
-- Source: https://leetcode.com/problems/average-time-of-process-per-machine/description/?envType=study-plan-v2&envId=top-sql-50
2+
3+
-- Table: Activity
4+
5+
-- +----------------+---------+
6+
-- | Column Name | Type |
7+
-- +----------------+---------+
8+
-- | machine_id | int |
9+
-- | process_id | int |
10+
-- | activity_type | enum |
11+
-- | timestamp | float |
12+
-- +----------------+---------+
13+
-- The table shows the user activities for a factory website.
14+
-- (machine_id, process_id, activity_type) is the primary key (combination of columns with unique values) of this table.
15+
-- machine_id is the ID of a machine.
16+
-- process_id is the ID of a process running on the machine with ID machine_id.
17+
-- activity_type is an ENUM (category) of type ('start', 'end').
18+
-- timestamp is a float representing the current time in seconds.
19+
-- 'start' means the machine starts the process at the given timestamp and 'end' means the machine ends the process at the given timestamp.
20+
-- The 'start' timestamp will always be before the 'end' timestamp for every (machine_id, process_id) pair.
21+
22+
-- There is a factory website that has several machines each running the same number of processes. Write a solution to find the average time each machine takes to complete a process.
23+
24+
-- The time to complete a process is the 'end' timestamp minus the 'start' timestamp. The average time is calculated by the total time to complete every process on the machine divided by the number of processes that were run.
25+
26+
-- The resulting table should have the machine_id along with the average time as processing_time, which should be rounded to 3 decimal places.
27+
28+
-- Return the result table in any order.
29+
30+
------------------------------------------------------------------------------
31+
32+
-- SQL Schema
33+
34+
Create table If Not Exists Activity (machine_id int, process_id int, activity_type ENUM('start', 'end'), timestamp float)
35+
Truncate table Activity
36+
insert into Activity (machine_id, process_id, activity_type, timestamp) values ('0', '0', 'start', '0.712')
37+
insert into Activity (machine_id, process_id, activity_type, timestamp) values ('0', '0', 'end', '1.52')
38+
insert into Activity (machine_id, process_id, activity_type, timestamp) values ('0', '1', 'start', '3.14')
39+
insert into Activity (machine_id, process_id, activity_type, timestamp) values ('0', '1', 'end', '4.12')
40+
insert into Activity (machine_id, process_id, activity_type, timestamp) values ('1', '0', 'start', '0.55')
41+
insert into Activity (machine_id, process_id, activity_type, timestamp) values ('1', '0', 'end', '1.55')
42+
insert into Activity (machine_id, process_id, activity_type, timestamp) values ('1', '1', 'start', '0.43')
43+
insert into Activity (machine_id, process_id, activity_type, timestamp) values ('1', '1', 'end', '1.42')
44+
insert into Activity (machine_id, process_id, activity_type, timestamp) values ('2', '0', 'start', '4.1')
45+
insert into Activity (machine_id, process_id, activity_type, timestamp) values ('2', '0', 'end', '4.512')
46+
insert into Activity (machine_id, process_id, activity_type, timestamp) values ('2', '1', 'start', '2.5')
47+
insert into Activity (machine_id, process_id, activity_type, timestamp) values ('2', '1', 'end', '5')
48+
49+
-- MS SQL Server Code
50+
51+
SELECT e.machine_id, ROUND(AVG(e.timestamp-s.timestamp),3) AS 'processing_time'
52+
FROM Activity e
53+
JOIN Activity s
54+
ON e.machine_id = s.machine_ID
55+
AND e.process_id = s.process_ID
56+
AND e.activity_type = 'end'
57+
AND s.activity_type = 'start'
58+
GROUP BY e.machine_id

‎2-Basic-Joins/1934_Confirmation_Rate.sql

Lines changed: 66 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,66 @@
1+
-- Source: https://leetcode.com/problems/confirmation-rate/description/?envType=study-plan-v2&envId=top-sql-50
2+
3+
-- Table: Signups
4+
5+
-- +----------------+----------+
6+
-- | Column Name | Type |
7+
-- +----------------+----------+
8+
-- | user_id | int |
9+
-- | time_stamp | datetime |
10+
-- +----------------+----------+
11+
-- user_id is the primary key for this table.
12+
-- Each row contains information about the signup time for the user with ID user_id.
13+
14+
-- Table: Confirmations
15+
16+
-- +----------------+----------+
17+
-- | Column Name | Type |
18+
-- +----------------+----------+
19+
-- | user_id | int |
20+
-- | time_stamp | datetime |
21+
-- | action | ENUM |
22+
-- +----------------+----------+
23+
-- (user_id, time_stamp) is the primary key for this table.
24+
-- user_id is a foreign key with a reference to the Signups table.
25+
-- action is an ENUM of the type ('confirmed', 'timeout')
26+
-- Each row of this table indicates that the user with ID user_id requested a confirmation message at time_stamp and that confirmation message was either confirmed ('confirmed') or expired without confirming ('timeout').
27+
28+
-- The confirmation rate of a user is the number of 'confirmed' messages divided by the total number of requested confirmation messages. The confirmation rate of a user that did not request any confirmation messages is 0. Round the confirmation rate to two decimal places.
29+
30+
-- Write an SQL query to find the confirmation rate of each user.
31+
32+
-- Return the result table in any order.
33+
34+
------------------------------------------------------------------------------
35+
36+
-- SQL Schema
37+
38+
Create table If Not Exists Signups (user_id int, time_stamp datetime)
39+
Create table If Not Exists Confirmations (user_id int, time_stamp datetime, action ENUM('confirmed','timeout'))
40+
Truncate table Signups
41+
insert into Signups (user_id, time_stamp) values ('3', '2020-03-21 10:16:13')
42+
insert into Signups (user_id, time_stamp) values ('7', '2020-01-04 13:57:59')
43+
insert into Signups (user_id, time_stamp) values ('2', '2020-07-29 23:09:44')
44+
insert into Signups (user_id, time_stamp) values ('6', '2020-12-09 10:39:37')
45+
Truncate table Confirmations
46+
insert into Confirmations (user_id, time_stamp, action) values ('3', '2021-01-06 03:30:46', 'timeout')
47+
insert into Confirmations (user_id, time_stamp, action) values ('3', '2021-07-14 14:00:00', 'timeout')
48+
insert into Confirmations (user_id, time_stamp, action) values ('7', '2021-06-12 11:57:29', 'confirmed')
49+
insert into Confirmations (user_id, time_stamp, action) values ('7', '2021-06-13 12:58:28', 'confirmed')
50+
insert into Confirmations (user_id, time_stamp, action) values ('7', '2021-06-14 13:59:27', 'confirmed')
51+
insert into Confirmations (user_id, time_stamp, action) values ('2', '2021-01-22 00:00:00', 'confirmed')
52+
insert into Confirmations (user_id, time_stamp, action) values ('2', '2021-02-28 23:59:59', 'timeout')
53+
54+
-- MS SQL Server Code
55+
56+
SELECT
57+
s.user_id,
58+
ROUND(SUM(CASE
59+
WHEN c.action = 'confirmed' THEN 1.00
60+
WHEN s.time_stamp IS NULL THEN 0.00
61+
ELSE 0.00
62+
END) / COUNT(*), 2) AS confirmation_rate
63+
FROM Signups s
64+
LEFT JOIN Confirmations c
65+
ON s.user_id = c.user_id
66+
GROUP BY s.user_id

0 commit comments

Comments
 (0)
0