挑戰連續 100 天學習 SQL 語法, 除了可以複習之外, 也可以釐清很多不懂的地方。
Find employees who have the highest salary in each of the departments.
Department, Employee, Salary
SELECT
d.Name AS Department,
Sel.Name AS Employee,
Sel.Salary AS Salary
FROM
(
SELECT
Name,
Salary,
DepartmentId,
DENSE_RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS dr
FROM Employee
) AS Sel
INNER JOIN Department d
ON d.Id = Sel.DepartmentId
WHERE Sel.dr = 1
Write a SQL query to get the nth highest salary from the Employee table
getNthHighestSalary(2)
CREATE FUNCTION getNthHighestSalary(@N INT) RETURNS INT AS
BEGIN
RETURN (
SELECT DISTINCT
Salary
FROM(
SELECT
DENSE_RANK() OVER(
ORDER BY Salary DESC
) AS rank
,
Salary
FROM Employee
) e
WHERE rank = @N
);
END
Find all dates' id with higher temperature compared to its previous dates.
id
SELECT
w1.id
FROM weather w1
jOIN (
SELECT
*,
DATEADD(
DAY,
1,
(Recorddate)
) lastday
FROM weather) w2
ON w1.recorddate = w2.lastday
WHERE w1.temperature > w2.temperature
login_date, user_count
SELECT
activity_date AS login_date,
COUNT(DISTINCT user_id) AS user_count
FROM (
SELECT
user_id,
activity,
min(activity_date) AS activity_date
FROM traffic
GROUP BY user_id,activity) t
GROUP BY activity_date,activity
HAVING activity ='login'
AND activity_date >= dateadd(
day,
-90,
'2019-06-30'
)
Find all numbers that appear at least three times consecutively.
ConsecutiveNums
WITH Sel AS
(
SELECT
Num,
LAG(Num, 1) OVER (ORDER BY Id) AS Prev,
LEAD(Num, 1) OVER (ORDER BY Id) AS Next
FROM Logs
)
SELECT DISTINCT
Num AS ConsecutiveNums
FROM Sel
WHERE Next = Num
AND Prev = Num
- Find the name of the user who has rated the greatest number of movies
- Find the movie name with the highest average rating in February 2020
result
SELECT
*
FROM (
SELECT TOP 1
users.name as results
FROM movie_rating
INNER JOIN users
ON users.user_id = movie_rating.user_id
GROUP BY users.name
ORDER BY COUNT(movie_id) desc, users.name
) AS t1
UNION ALL
SELECT
*
FROM (
SELECT TOP 1
movies.title AS results
FROM movies
INNER JOIN movie_rating
ON movies.movie_id = movie_rating.movie_id
WHERE month(movie_rating.created_at) = 2
GROUP BY movies.title
ORDER BY CAST(SUM(rating) AS float) / COUNT(movies.title) desc, movies.title
) AS t2
Find the IDs of the invalid tweets.
tweet_id
SELECT
tweet_id
FROM Tweets
WHERE len(content) > 15;
Write an SQL query to report the distance travelled by each user.
name, travelled_distance
SELECT
u.name,
isnull(
sum(
r.distance
),
0
) AS travelled_distance
FROM Users u
LEFT JOIN Rides r
ON u.id = r.user_id
GROUP BY u.name
ORDER BY travelled_distance DESC, u.name
Find the id and the name of active users.
id, name
SELECT DISTINCT
c.id,
name
FROM(
SELECT
id,
COALESCE(
DATEDIFF(
day,
start_date,
fifth_date
),
0
) AS diff
FROM(
SELECT
id,
login_date AS start_date,
LEAD(
login_date,
4
) OVER(
partition BY id
ORDER BY login_date
) AS fifth_date
FROM(
SELECT DISTINCT
id,
login_date
FROM logins
) a
)b
)c ,
accounts d
WHERE diff = 4
AND c.id = d.id
ORDER BY c.id
Write a query to rank scores
score, Rank
SELECT
Score,
DENSE_RANK() OVER(
ORDER BY Score
DESC
) AS rank
FROM Scores
country
WITH CTE AS (
SELECT
CNT.name AS CNTR_NM,
CR.duration
FROM Person P
JOIN Calls CR
ON P.id = CR.caller_id
OR P.id = CR.callee_id
JOIN Country CNT
ON CNT.country_code = LEFT(P.phone_number, 3)
)
SELECT
CNTR_NM AS country
FROM CTE
GROUP BY CNTR_NM
HAVING AVG(duration) > (
SELECT
AVG(duration) AS Glob_AVG
FROM CTE
)
Return the number of distinct lead_id's and distinct partner_id's.
date_id, make_name, unique_leads, unique_partners
SELECT
ds.date_id,
ds.make_name,
COUNT(DISTINCT lead_id) AS unique_leads,
COUNT(DISTINCT partner_id) AS unique_partners
FROM DailySales ds
GROUP BY ds.date_id, ds.make_name
Find the name and balance of users with a balance higher than 10000.
name, balance
SELECT
Users.name AS name,
SUM(Transactions.amount) AS balance
FROM Transactions
LEFT JOIN Users
ON Transactions.account = Users.account
GROUP BY Users.name
HAVING SUM(Transactions.amount) > 10000
Find the IDs of the users who visited without making any transactions and the number of times they made these types of visits.
customer_id, count_no_trans
SELECT
v.customer_id,
COUNT(1) AS count_no_trans
FROM Visits v
LEFT JOIN Transactions t
ON v.visit_id = t.visit_id
WHERE t.transaction_id IS NULL
GROUP BY v.customer_id
Find the actors and directors who cooperated at Least three times
actor_id, director_id
SELECT
actor_id,
director_id
FROM ActorDirector
GROUP BY actor_id, director_id
HAVING COUNT(director_id) > 2
Write an SQL query to report, How much cubic feet of volume does the inventory occupy in each warehouse
warehouse_name, volume
SELECT
a.name AS warehouse_name,
SUM(
Width * Length * Height * units
) AS volume
FROM Warehouse a
JOIN Products b
ON a.product_id = b.product_id
GROUP BY a.name
with new_table as (
select
host_team,
guest_team,
case when host_goals = guest_goals then 1 when host_goals < guest_goals then 0 else 3 end as host_points,
case when host_goals = guest_goals then 1 when host_goals > guest_goals then 0 else 3 end as guest_points
from
matches
)
select
*
from
(
select
t.team_id as team_id,
t.team_name as team_name,
coalesce(b.num_points, 0) as num_points
from
(
select
team_id,
sum(total_points) as num_points
from
(
select
host_team as team_id,
sum(host_points) as total_points
from
new_table
group by
host_team
union all
select
guest_team as team_id,
sum(guest_points) as total_points
from
new_table
group by
guest_team
) a
group by
team_id
) b
right join teams t on b.team_id = t.team_id
) c
order by
c.num_points desc,
c.team_id
Find the number of times each student attended each exam
student_id, student_name, subject_name, attended_exams
SELECT dd.student_id, dd.student_name, dd.subject_name, ISNULL( count(dd.cnt) , 0 ) AS attended_exams FROM ( SELECT s.student_id, s.student_name, sub.subject_name, e.student_id AS cnt FROM students s CROSS JOIN subjects sub LEFT JOIN examinations e ON s.student_id = e.student_id AND e.subject_name = sub.subject_name ) AS dd GROUP BY dd.student_id, dd.student_name, dd.subject_name
Use VSCode to create and run T-SQL Script
Find the shortest distance in a plane.
shortest
SELECT DISTINCT top 1
ROUND(
SQRT(
POWER(
startingPoint.x - endingPoint.x,
2
) + power(
startingPoint.y - endingPoint.y,
2
)
),
2
) AS shortest
FROM point_2d startingPoint
JOIN point_2d endingPoint
ON startingPoint.x <> endingPoint.x
OR startingPoint.y <> endingPoint.y
ORDER BY shortest
Create a Session Bar Chart
bin, total
SELECT
'[0-5>' AS bin,
SUM(
IIF(
duration/60 BETWEEN 0 AND 4,
1,
0
)
) AS total
FROM sessions
UNION
SELECT
'[5-10>' AS bin,
SUM(
IIF(
duration/60 BETWEEN 5 AND 9,
1,
0
)
) AS total FROM sessions
UNION
SELECT
'[10-15>' AS bin,
SUM(
IIF(
duration/60 BETWEEN 10 AND 14,
1,
0
)
) AS total
FROM sessions
UNION
SELECT
'15 or more' AS bin ,
SUM(
IIF(
duration / 60 >= 15,
1,
0
)
) AS total FROM sessions
Create a Stored Procedure
Find the customer who placed maximum numbers of orders
customer_number
SELECT TOP 1
customer_number
FROM orders
GROUP BY customer_number
ORDER BY COUNT(*) DESC
Find the consecutive available seats
seat_id
SELECT DISTINCT
c.seat_id
FROM cinema c
WHERE c.free = 1
AND EXISTS (
SELECT
1
FROM cinema
WHEREfree = 1
AND (
seat_id = c.seat_id - 1
OR seat_id = c.seat_id + 1
)
)
ORDER BY c.seat_id
Find all the people who viewed more than one article on the same date.
id
SELECT DISTINCT
viewer_id id
FROM Views
GROUP BY viewer_id, view_date
HAVING COUNT(DISTINCT article_id) > 1
Unable to start SQL Server
Excluding the port 1433 with command "netsh int ipv4 add excludedportrange tcp startport=1433 numberofports=1 store=persistent"
The port 1433 is occupied by Windows OS dynamically assigned port
Reformat the table such that there is a department id column and a revenue column for each month.
id, Jan_Revenue, Feb_Revenue, Mar_Revenue, Apr_Revenue, May_revenue, June_Revenue, July_Revenue, Aug_Revenue, Sept_Revenue, Oct_Revenue, Nov_Revenue, Dec_Revenue
SELECT
id,
[Jan] AS Jan_Revenue,[Feb] AS Feb_Revenue,[Mar] AS Mar_Revenue,[Apr] AS Apr_Revenue,
[May] AS May_Revenue,[Jun] AS Jun_Revenue,[Jul] AS Jul_Revenue,[Aug] AS Aug_Revenue,
[Sep] AS Sep_Revenue,[Oct] AS Oct_Revenue,[Nov] AS Nov_Revenue,[Dec] AS Dec_Revenue
FROM(
SELECT
id,
revenue,
month
FROM Department
) AS dep
PIVOT
(
MAX(revenue)
FOR month IN ([Jan],[Feb],[Mar],[Apr],
[May],[Jun],[Jul],[Aug],
[Sep],[Oct],[Nov],[Dec])
) AS ReFORmat
Find the shortest distance between two points in these points.
query_name, quality, poor_query_percentage
Find the distance between any two points on x-axis << ABS("startingPoint" - "sndingPoint")"" join "table""startingPoint" != "endingPoit"
SELECT
MIN(ABS(startingPoint.x - endingPoint.x)) AS shortest
FROM point startingPoint
JOIN point endingPoint
ON startingPoint.x != endingPoint.x
Find the percentage of immediate orders in the table
immediate_percentage
SELECT
ROUND(
(
(
(
COUNT(t.delivery_id)
) * 1.0 / (
SELECT
(
COUNT(delivery_id)
)* 1.0
FROM
delivery
)
) * 100
),
2
) AS immediate_percentage
FROM
(
SELECT
delivery_id,
CASE
WHEN order_date = customer_pref_delivery_date THEN 'immediate'
else 'scheduled' END AS customerDeliveryDate
FROM
delivery
) AS t
WHERE
t.customerDeliveryDate = 'immediate'
Return table result in specific format.
product_name, sale_date, total
SELECT
product_name,
sale_date,
count(*) AS total
FROM(
SELECT
lower(ltrim(rtrim(product_name))) product_name,
convert(varchar(7),sale_date,126) AS sale_date
FROM Sales
WHERE year(sale_date) = '2000'
) T2
GROUP BY product_name,sale_date
ORDER BY product_name,sale_date
Find the type of weather in each country for November 2019
country_name, weather_type
SELECT
country_name,
CASE
WHEN AVG(CAST(Weather.weather_state AS FLOAT)) <= 15 THEN 'Cold'
WHEN AVG(CAST(Weather.weather_state AS FLOAT)) >= 25 THEN 'Hot'
ELSE 'Warm'
END AS weather_type
FROM Countries
LEFT JOIN Weather
ON Countries.country_id = Weather.country_id
WHERE Weather.day
BETWEEN '11/01/2019'
AND '11/30/2019'
GROUP BY country_name
output movies with an odd numbered ID and a description that is not 'boring'. Order the result by rating.
id, movie, description, rating
SELECT
id,
movie,
description,
rating
FROM cinema
WHERE id % 2 != 0
AND description != 'boring'
ORDER By rating DESC
Find big country
name, population, area
SELECT
name,
population,
area
FROM World
WHERE area > 3000000
OR population > 25000000
get the names of products with greater than or equal to 100 units ordered in February 2020 and their amount
product_name, unit
SELECT
product_name,
unit
FROM Products T1
INNER JOIN(
SELECT
product_id,
SUM(unit) as unit
FROM Orders
WHERE (
order_date >= '2020-02-01'
AND order_date < '2020-03-01'
)
GROUP BY product_id
) T2
ON T1.product_id = T2. product_id
WHERE unit >= 100
Swap all f and m values with a single update statement and no intermediate temp table
id, name, sex, salary
sex change
UPDATE salary SET sex = ( CASE WHEN sex = 'f' THEN 'm' WHEN sex = 'm' THEN 'f' END )
Find the user who have valid emails.
user_id, name, mail
may contain_LIKE '%[^]%'
SELECT
*
FROM Users
WHERE (
mail LIKE '[a-z]%'
)AND(
LEFT(mail, LEN(mail) - 13) NOT LIKE '%[^0-9a-zA-Z_.-]%'
)AND(
mail LIKE '%@leetcode.com'
)
Query to check whether these three line segments can form a triangle
x, y, z, triangle
CASE + AND
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
Find the number of comments per post
post_id, number_of_comments
SELECT DISTINCT
T1.sub_id AS post_id,
ISNULL(T2.number_of_comments,0) AS number_of_comments
FROM submissions T1
LEFT JOIN
(
SELECT DISTINCT
parent_id,
count(DISTINCT sub_id) AS number_of_comments
FROM submissions
WHERE parent_id IS NOT NULL
GROUP BY parent_id
) T2
ON T1.sub_id = T2.parent_id
WHERE T1.parent_id IS NULL
Return the list of customers NOT referred by the person with id '2'
name
SELECT
name
FROM customer
WHERE referee_id != 2
OR referee_id IS NULL
Find the prices of all products on 2019-08-16
product_id, price
SELECT DISTINCT
T1.product_id,
isnull(T2.new_price, 10) AS price
FROM products T1
LEFT JOIN
(
SELECT
a.product_id,
a.new_price,
a.change_date
FROM Products a
WHERE a.change_date IN
(
SELECT
MAX(b.change_date)
FROM Products b
WHERE b.product_id = a.product_id
AND b.change_date <= '2019-08-16'
GROUP BY b.product_id
)
) T2
ON T1.product_id = T2.product_id
Find all the authors that viewed at least one of their own articles
id
SELECT DISTINCT
SELECT DISTINCT
viewer_id AS id
FROM Views
WHERE author_id = viewer_id
Find the teams size of each of the employees
employee_id, team_size
- INNER JOIN + Subquery
- Count() + group by
SELECT
employee_id,
team_size
FROM employee
INNER JOIN(
SELECT
team_id, COUNT(employee_id) AS team_size
FROM Employee
GROUP BY team_id
)Team
ON employee.team_id = Team.team_id
ORDER BY employee_id
Select all employee's name and bonus whose bonus is < 1000.
name, bonus
LEFT JOIN + OR IS NULL
SELECT
name,
bonus
FROM Employee T1
LEFT JOIN Bonus T2
ON T1.empId = T2.empId
WHERE bonus < 1000 OR bonus IS NULL
找出收入高於主管的員工
Employee
Subquery
SELECT
Name as Employee
FROM
Employee E1
WHERE
E1.salary > (
SELECT
E2.salary
FROM
Employee E2
WHERE
E1.ManagerId = E2.Id
)
找出沒有下訂的顧客
Customers
LEFT JOIN + IS NULL
SELECT
Name AS Customers
FROM
Customers LEFT JOIN Orders
ON Customers.Id = Orders.CustomerId
WHERE
Orders.CustomerId IS NULL
找出重複的電子郵件
GROUP BY <column_name> HAVING COUNT(<column_name>) > 1
SELECT
Email AS Email
FROM
Person
GROUP BY Email
HAVING COUNT(Email) > 1
使用 MAX() 找出最高的薪水, 在小於最高薪的資料中再找出最高薪。
找出第二高薪
SELECT
MAX(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary < (
SELECT
MAX(Salary)
FROM Employee
)
查找指定欄位
SELECT
FirstName,
LastName,
City,
State
FROM Address right join Person ON Address.PersonId = Person.PersonId
今天參考網友的做法做了一次, 細節在研究
查詢連續兩日都登入的玩家比例
SELECT
ROUND(
CAST(
COUNT(
DISTINCT CASE WHEN DATEDIFF(DAY,d1,d2)=1
THEN player_id
ELSE null END
) AS DECIMAL(18,2)
)/COUNT(DISTINCT player_id),
2
) AS fraction
FROM(
SELECT
player_id,
event_date d1,
lead(event_date,1)
OVER(
PARTITION BY player_id
ORDER BY rk
) d2
FROM(
SELECT
player_id,
event_date,
DENSE_RANK()OVER(
PARTITION BY player_id
ORDER BY event_date
) AS rk
FROM Activity
) t
WHERE rk=1 or rk=2) t
今天是 Game play analysis 的第三題, 使用 SUM over 的方式來解題。
查詢每個時間點上該名員工已經玩了多少次遊戲
SELECT
player_id,
event_date,
sum(games_played)
over(
partition by player_id order by event_date
) as games_played_so_far
FROM Activity;
SELECT
player_id,
MIN(event_date) AS first_login
FROM Activity
GROUP BY player_id
SELECT
player_id,
device_id
FROM (SELECT
player_id,
device_id,
RANK() OVER (
PARTITION BY player_id ORDER BY event_date
) AS RANK
FROM Activity
)T2
WHERE RANK=1
SELECT
S.product_id, P.product_name
FROM sales S, product P
WHERE S.product_id = P.product_id
GROUP BY S.product_id, P.product_name
HAVING MIN(S.sale_date) >= '2019-01-01'
AND MAX(S.sale_date) <= '2019-03-31'
今天使用EXCEPT 來解這題, 雖然可以過關, 不過需要其他更好的解法
SELECT
buyer_id
FROM sales s, product p
WHERE s.product_id=p.product_id and p.product_name='S8'
EXCEPT
SELECT
buyer_id
FROM sales s, product p
WHERE s.product_id=p.product_id and p.product_name='iPhone'
今天使用 GROUP BY 結合 RANK() 進行分組排序, 找出銷售總額最高的 seller
SELECT
seller_id
FROM(
SELECT
seller_id,
RANK()OVER(
ORDER BY SUM(price) DESC
) AS totalRank
FROM Sales
GROUP BY seller_id
) AS s
WHERE totalRank = 1
今天使用 GROUP BY 加上 INNER JOIN 完成
SELECT
S1.product_id,
S1.year AS first_year,
S1.quantity,
S1.price
FROM sales S1
INNER JOIN (
SELECT
S2.product_id,
min(S2.year) AS year
FROM sales S2
GROUP BY S2.product_id
) AS M
ON S1.product_id = M.product_id AND S1.year = M.year
特別要注意的是, 在 II 中使用 GROUP BY 加上 SUM() 的用法, 雖然可以通過, 但還是會有 Time Limit Exceeded 的風險。其他更好的解法之後再做紀錄。
SELECT
product_name,
year,
price
FROM Product P1
INNER JOIN Sales S1
ON P1.product_id = S1.product_id
SELECT
product_id,
SUM(quantity) AS total_quantity
FROM Sales
GROUP BY product_id
延續前兩天的題目, 今天終於把 Project Emplmoyee 系列的最後一題結束了。 這題的解法是先用 LEFT JOIN 找出各專案最高經驗年數, 然後再做一次 LEFT JOIN 找出所對應的工號
SELECT
Pid1 AS project_id,
Emp1 AS employee_id
FROM(
SELECT
P1.project_id AS Pid1,
P1.employee_id AS Emp1,
experience_years AS Years1
FROM Project P1
INNER JOIN Employee E1
ON P1.employee_id = E1.employee_id) T1
INNER JOIN(
SELECT -- 1>找出專案最多經驗
P2.project_id AS Pid2,
MAX(E2.experience_years) AS Years2
FROM Employee E2
LEFT JOIN Project P2
ON E2.employee_id = P2.employee_id
GROUP BY project_id) T2
ON T1.Pid1 = T2.Pid2 AND T1.Years1 = T2.Years2
該不會要來個一題多解?!😲
SELECT
project_id
FROM
Project
GROUP BY project_id
HAVING COUNT(employee_id) = (
SELECT TOP 1
COUNT(employee_id)
FROM
Project
GROUP BY project_id
最近蠻常解 LeetCode 的題目, 所以第一天就從 LeetCode 開始吧!!
SELECT
project_id,
Round(
Avg(
CAST(
experience_years AS decimal(10,2)
)
),
2
) AS average_years
FROM Project P
INNER JOIN Employee E
ON P.employee_id = E.employee_id
GROUP BY project_id
