data_yep

[문제]  [MySQL]SELECT s.student_id , s.student_name , sub.subject_name , COUNT(e.student_id) AS attended_examsFROM Students AS s CROSS JOIN Subjects AS sub LEFT JOIN Examinations AS e ON s.student_id = e.student_id AND sub.subject_name = e.subject_nameGROUP BY s.student_id, sub.subject_nameORDER BY s.student_id, sub.subject_name

[문제]  [MySQL]SELECT p.product_id , IFNULL(ROUND(SUM(p.price * u.units) / SUM(u.units), 2), 0) AS average_priceFROM Prices AS p LEFT JOIN UnitsSold AS u ON p.product_id = u.product_id AND (u.purchase_date BETWEEN p.start_date AND p.end_date)GROUP BY product_id

[문제]  [MySQL]WITH evaluation AS ( SELECT * , rating / position AS quality , CASE WHEN rating

[문제]  [MySQL]WITH Total_weight AS ( SELECT * , SUM(weight) OVER (ORDER BY turn ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumsum FROM Queue)SELECT person_nameFROM Total_weightWHERE CUMSUM

[문제]  [MySQL]WITH Transactions_preprocessed AS ( SELECT * , DATE_FORMAT(trans_date, '%Y-%m') AS trans_month FROM Transactions)SELECT trans_month AS month , country , COUNT(DISTINCT id) AS trans_count , COUNT(DISTINCT CASE WHEN state = 'approved' THEN id END) AS approved_count , SUM(amount) AS trans_total_amount , SUM(CASE WHEN state = 'approved' THEN amount ELSE ..

[문제]  [MySQL]SELECT id , SUM(CASE WHEN month = 'Jan' THEN revenue ELSE NULL END) AS Jan_Revenue , SUM(CASE WHEN month = 'Feb' THEN revenue ELSE NULL END) AS Feb_Revenue , SUM(CASE WHEN month = 'Mar' THEN revenue ELSE NULL END) AS Mar_Revenue , SUM(CASE WHEN month = 'Apr' THEN revenue ELSE NULL END) AS Apr_Revenue , SUM(CASE WHEN month = 'May' THEN revenue ELSE NULL END) AS Ma..

[문제]  [MySQL]WITH order_timeline AS ( SELECT * , RANK() OVER (PARTITION BY customer_id ORDER BY order_date ASC) AS rnk FROM Delivery)SELECT ROUND(AVG(CASE WHEN order_date = customer_pref_delivery_date THEN 1 ELSE 0 END) * 100, 2) AS immediate_percentageFROM order_timelineWHERE rnk = 1

[문제]  [MySQL]SELECT product_id , new_price AS priceFROM ProductsWHERE (product_id, change_date) IN (SELECT product_id , MAX(change_date) FROM Products WHERE change_date

[문제]  [MySQL]SELECT u.user_id AS buyer_id , u.join_date , SUM(CASE WHEN order_date BETWEEN '2019-01-01' AND '2019-12-31' THEN 1 ELSE 0 END) AS orders_in_2019FROM Users AS u LEFT JOIN Orders AS o ON u.user_id = o.buyer_idGROUP BY u.user_id

[문제]  [MySQL]SELECT DISTINCT author_id AS idFROM ViewsWHERE author_id = viewer_idORDER BY id ASC