[SQL 코드카타] 124~126번(LeetCode)

124번 group-sold-products-by-the-date

> Group_concat 활용법

SELECT
    sell_date,
    COUNT(DISTINCT product) AS num_sold,
    GROUP_CONCAT(DISTINCT product ORDER BY product ASC SEPARATOR ',' ) AS products
FROM 
    Activities
GROUP BY
    sell_date    
ORDER BY
    sell_date

 

125번 list-the-products-ordered-in-a-period

SELECT
    p.product_name,
    a.unit
FROM
    Products p
    JOIN (SELECT
            product_id, sum(unit) AS unit
        FROM
            Orders
        WHERE
            DATE_FORMAT(order_date, '%Y-%m') = '2020-02'
        GROUP BY
            product_id) a
    ON p.product_id = a.product_id
WHERE 
    a.unit >= 100

 

126번 find-users-with-valid-e-mails

>^맨 앞에[A~Z a~z]

> [ 속한 문자들이 ] * 0번 혹은 여러 번

> $를 통해 @~~~.com문자로 끝나도록 

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