[12주차]리트코드: Day9( 1393) ~ Day10

182. Duplicate Emails

Write an SQL query to report all the duplicate emails. Note that it's guaranteed that the email field is not NULL.

select email
from Person
group by email
having count(email) > 1

group by와 having절을 사용해 조건에 해당하는 결과를 추출한다.

 

 

1050. Actors and Directors Who Cooperated At Least Three Times

Write a SQL query for a report that provides the pairs (actor_id, director_id) where the actor has cooperated with the director at least three times.

select actor_id, 
       director_id
from ActorDirector
group by actor_id, director_id
having count(timestamp) >= 3

 

 

 

1587. Bank Account Summary II

Write an SQL query to report the name and balance of users with a balance higher than 10000. The balance of an account is equal to the sum of the amounts of all transactions involving that account.

select Users.name as name,
       sum(Transactions.amount) as balance
from Transactions
inner join Users on Transactions.account = Users.account
group by Transactions.account
having sum(Transactions.amount) > 10000

 

 

 

1084. Sales Analysis III

Write an SQL query that reports the products that were only sold in the first quarter of 2019. That is, between 2019-01-01 and 2019-03-31 inclusive.

select distinct db.product_id,
       p.product_name
from (
select *
from Sales
where sale_date between '2019-01-01' and '2019-03-31'
) db
inner join Product p on db.product_id = p.product_id 
where db.product_id not in (select product_id from Sales where sale_date not between '2019-01-01' and '2019-03-31')

 

 

 

1393. Capital Gain/Loss

Write an SQL query to report the Capital gain/loss for each stock.

The Capital gain/loss of a stock is the total gain or loss after buying and selling the stock one or many times.

select stock_name,
       sum(case when operation = "Buy" then -price else price end) as capital_gain_loss 
from Stocks
group by 1
order by 1