[코드카타 스터디]sql_6(123, 124, 126 문제)

Arin lee·2024년 11월 1일

문제링크

문제 123. Write a solution to find the second highest distinct salary from the Employee table. If there is no second highest salary, return null (return None in Pandas).

1.
select
    ifnull(
      (
        select distinct Salary
        from Employee
        order by Salary desc
        limit 1 offset 1
        ),
    null
    ) SecondHighestSalary
    
 
->offset사용

2.
SELECT max(salary) SecondHighestSalary
FROM employee
WHERE salary not in (SELECT max(salary) FROM employee);

3.
SELECT MAX(salary) SecondHighestSalary FROM Employee 
WHERE salary < (SELECT MAX(salary) FROM Employee)

4.
with a as (
select id, salary, dense_rank() over(order by salary desc) rn
from Employee
)

select(
select distinct salary
from a 
where rn = 2
) as SecondHighestSalary 

5.
select max(salary) as SecondHighestSalary
from employee
where salary != (select max(salary) from employee)

문제 124. Write a solution to find for each date the number of different products sold and their names.
The sold products names for each date should be sorted lexicographically.
Return the result table ordered by sell_date.

1.
select sell_date, count(distinct product)num_sold, 
       group_concat(distinct product)products 
from Activities
group by 1
order by 1;

2.
SELECT sell_date, 
			 count(distinct product) num_sold, 
			 group_concat(distinct product order by product) products
FROM activities
GROUP BY 1
ORDER BY 1;

3.
SELECT 
    sell_date, 
    count(distinct product) as num_sold, 
    group_CONCAT(distinct product order by product) as products
from Activities
group by sell_date
order by sell_date;

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

5.
with unique_product as (
    select distinct sell_date, product
    from activities
)
select sell_date, num_sold, group_concat(product order by product) as products
from(   select *, count(product) over(partition by sell_date) as num_sold
        from unique_product
    )q1
group by sell_date

문제126. Write a solution to find the users who have valid emails.
A valid e-mail has a prefix name and a domain where:
The prefix name is a string that may contain letters (upper or lower case), digits, underscore '_', period '.', and/or dash '-'. The prefix name must start with a letter.
The domain is '@leetcode.com'.

1.
SELECT *
FROM Users
WHERE mail REGEXP '^[a-zA-Z][a-zA-Z0-9_.-]*@leetcode[.]com$';

2.
SELECT *
FROM users
WHERE mail REGEXP '^[:alpha:][a-zA-Z0-9_.-]*@leetcode[.]com$';


3.
select * from Users 
WHERE mail REGEXP '^[a-zA-Z][a-zA-Z0-9_.-]*@leetcode[.]com$';

SELECT *
FROM Users
WHERE REGEXP_LIKE(mail, '^[A-Za-z]+[A-Za-z0-9\_\.\-]*@leetcode\\.com$');

4.
select user_id, name, mail
from users
where regexp_replace(replace(mail, '@leetcode.com', ''), '[0-9A-Za-z._-]', '') = '' and
      substr(mail, -13) = '@leetcode.com' and
      substr(mail, 1, 1) regexp '[A-Za-z]'
      
5.
# 지백
select *
from users
WHERE mail REGEXP '^[:alpha:][a-zA-Z0-9_.-]*@leetcode[\.]com$';

# []안에 있으면 문자로, 근데 .....음 \ 왜. ???? 속도 다름? 

# 예지
SELECT *
FROM users
WHERE mail REGEXP '^[:alpha:][a-zA-Z0-9_.-]*@leetcode[.]com$';