SQL 코테 준비 (4월 1주)(리트코드)

3.28

1) Group Sold Products By the Date

select sell_date,
       count(distinct product) as num_sold,
       group_concat(distinct product order by product) AS products
from Activities
group by sell_date

• group_concat : GROUP BY 칼럼A 를 기준으로 칼럼 B 의 각 행을 묶어서 하나의 행에 나열해주는 함수 (GROUP BY 사용 필수)
  - GROUP_CONCAT(DISTINCT): 중복 없이 나열
  - GROUP_CONCAT(ORDER BY): 나열 순서 지정 (오름차순 디폴트)
  - GROUP_CONCAT(SEPARATOR '문자'): 구분 문자 (쉼표 디폴트)

2) Find users with Valid E-mails

select user_id, name, mail from Users 
where mail like '^[a-zA-Z][a-zA-Z0-9_.-]*+@leetcode\\.com$'

3) Patients with a Condition

select *
from patients
where conditions like 'DIAB1%' or conditions like '% DIAB1%'

3.29

1) Customer Who visited but did not make any transactions

select v.customer_id,
       count(*) as count_no_trans
from visits as v
left join Transactions as t on v.visit_id = t.visit_id
where t.transaction_id is null
group by v.customer_id

2) Bank Account Summary II

select u.name,
       sum(t.amount) as balance
from users as u
join transactions as t on u.account = t.account
group by u.name
having sum(t.amount) > 10000

3) Percentage of Users attended a Contest

select 
    r.contest_id,
    round( count(u.user_id) / (select count(*) from Users)  * 100, 2)
    as percentage 
from 
    Users as u 
right join 
    Register as r on u.user_id = r.user_id 
group by 
    r.contest_id 
order by 
    percentage desc, r.contest_id

3.30

1) Average Time of process per Machine

select machine_id,
       round(avg(processing_time),3) as processing_time
from (
    select machine_id,
           process_id,
           max(timestamp) - min(timestamp) as processing_time
    from activity
    group by machine_id, process_id
) as t
group by machine_id

2) Fix names in a table

select user_id,
       concat(upper(substr(name, 1,1)), lower(substr(name, 2))) as name
from users
order by user_id

3) Invalid Tweets

select tweet_id
from tweets
where length(content) > 15

3.31

1) Daily Leads and Partners

select date_id,
       make_name,
       count(distinct lead_id) as unique_leads,
       count(distinct partner_id) as unique_partners
from DailySales
group by date_id, make_name

2) Find Followers Count

select user_id,
       count(follower_id) as followers_count
from followers
group by user_id
order by user_id asc

3) The Number of Employees which report to each employee

select e2.employee_id,
       e2.name,
       count(e1.reports_to) as reports_count,
       round(avg(e1.age)) as average_age
from employees as e2
left join employees as e1 on e1.reports_to = e2.employee_id
group by employee_id
having reports_count > 0 and average_age > 0
order by employee_id

4.1

1) Find total time spent by each employee

select event_day as day,
       emp_id,
       sum(time) as total_time
from (
    select emp_id,
           event_day,
           out_time - in_time as time
    from employees
) as t
group by day, emp_id

2) Recyclable and low fat products

select product_id
from products
where low_fats = 'Y' and recyclable = 'Y'

3) Primary Department for each employee

(SELECT
    employee_id,
    department_id
FROM
    Employee
GROUP BY 1
HAVING COUNT(department_id) = 1)
UNION ALL
(SELECT
    employee_id,
    department_id
FROM
    Employee
WHERE primary_flag = 'Y'
ORDER BY employee_id)

4.2

1) Rearrange Products Table

select product_id, 'store1' as store, store1 as price
from products
where store1 is not null
union
select product_id, 'store2' as store, store2 as price
from products
where store2 is not null
union
select product_id, 'store3' as store, store3 as price
from products
where store3 is not null;

2) Calculate Special Bonus

select employee_id,
            case when mod(employee_id,2) = 1 and name not like 'M%' then salary
            else 0
            end as bonus
from employees
order by employee_id

3) The Latest Login in 2020

select user_id,
       max(time_stamp) as last_stamp
from logins
where time_stamp between '2020-01-01 00:00:00' and '2020-12-31 23:59:59'
group by user_id