Leetcode--SQL刷题(1149-1193)

-- 1179 常规透视列方法
-- 1193 DATE_FORMAT(trans_date,'%Y-%m')函数使用 

1149. Article Views II

# 0.33
# 注意count（）中应该是文章article的id，不能是count（*）会存在重复行。
# 题目中问的也是 more than one article
select distinct viewer_id id 
from views
group by viewer_id,view_date
having count(distinct article_id)>1
order by id

1158. Market Analysis I

# 自己的方法：0.8667
select user_id buyer_id, join_date, ifnull(num,0) orders_in_2019
from
    users left join 
    (select buyer_id, count(*) num
    from orders
    where order_date between date('2019-01-01') and date('2019-12-31')
    group by buyer_id) t
on users.user_id=t.buyer_id

1159. Market Analysis II

# 注意一个问题：两次都是left join, 尽管第二个表的第三个表之间是 inneer join
# 0.6344
select a.user_id seller_id, case when a.favorite_brand=b.item_brand then 'yes'
                                 else 'no' end 2nd_item_fav_brand
from
    users a 
left join 
    (select order_date, item_id, seller_id, case when @id=seller_id then @cnt:=@cnt+1
                                                 when @id:=seller_id then @cnt:=1 end cnt
    from orders,(select @id:=null, @cnt:=null) t1
    order by seller_id,order_date) t3
on t3.cnt=2 and a.user_id=t3.seller_id
left join items b 
on t3.item_id=b.item_id
order by seller_id

1164. Product Price at a Given Date


我对这个题产生了误解，其中一个条件是：如果该产品在2019-8-16之前没有最新价格，默认为10

# 常规方法 0.3402
# 通过select distinct product_id from Products 获取完整产品表的思路真的很好
select a.product_id,ifnull(b.new_price,10) as price
from
    (select distinct product_id from Products) a 
left join
(select product_id, new_price from Products 
where (product_id,change_date) in (select product_id,max(change_date) as change_date from Products 
                                  where change_date<='2019-08-16' group by product_id)) b
on a.product_id=b.product_id

# 方法2 0.74  思路很好
SELECT product_id, new_price AS price
FROM Products
WHERE (product_id, change_date) IN (
                                    SELECT product_id, MAX(change_date)
                                    FROM Products
                                    WHERE change_date <= '2019-08-16'
                                    GROUP BY product_id)

UNION all

SELECT DISTINCT product_id, 10 AS price
FROM Products
WHERE product_id NOT IN (SELECT product_id FROM Products WHERE change_date <= '2019-08-16')

# 方法3 0.9125 写法确实漂亮
# SUM(CASE WHEN b.change_date IS NOT NULL THEN new_price ELSE 0 END) 很好
SELECT a.product_id, CASE WHEN MAX(b.change_date) IS NULL THEN 10
		                  ELSE SUM(CASE WHEN b.change_date IS NOT NULL THEN new_price ELSE 0 END) END AS price
FROM Products a
LEFT JOIN 

    (SELECT product_id, MAX(change_date) AS change_date
    FROM Products a
    WHERE a.change_date <= '2019-08-16'
    GROUP BY product_id) b

ON a.product_id = b.product_id AND a.change_date = b.change_date
GROUP BY a.product_id

1173. Immediate Food Delivery I

# 一道简单题，但是自己的方法不好。0.33
select round(number1/count(delivery_id)*100,2) immediate_percentage 
from delivery,
    (select count(*) number1
    from delivery
    where order_date= customer_pref_delivery_date) t

# 注意sum函数的使用方式，和上个题有相同思路 0.87
SELECT round(SUM(CASE WHEN a.order_date = a.customer_pref_delivery_date THEN 1 ELSE 0 END) 
                                    * 100 / COUNT(*), 2) AS immediate_percentage
FROM Delivery a

# 方法3：也可以这么写，但是感觉不太好
SELECT ROUND(sum(order_date=customer_pref_delivery_date)/count(*)*100,2) AS immediate_percentage
FROM Delivery

1174. Immediate Food Delivery II

# 自己的方法 0.85
select round(SUM(CASE WHEN a.order_date = a.customer_pref_delivery_date THEN 1 ELSE 0 END) 
                                    * 100 / COUNT(*), 2) immediate_percentage
from delivery a
join
    (select customer_id,min(order_date) order_date
    from delivery
    group by customer_id) b 
on a.customer_id=b.customer_id and a.order_date=b.order_date

1179. 重新格式化部门表

# 方法1
SELECT DISTINCT id AS "id",
SUM(IF (month = "Jan", revenue, null)) AS "Jan_Revenue",
SUM(IF (month = "Feb", revenue, null)) AS "Feb_Revenue",
SUM(IF (month = "Mar", revenue, null)) AS "Mar_Revenue",
SUM(IF (month = "Apr", revenue, null)) AS "Apr_Revenue",
SUM(IF (month = "May", revenue, null)) AS "May_Revenue",
SUM(IF (month = "Jun", revenue, null)) AS "Jun_Revenue",
SUM(IF (month = "Jul", revenue, null)) AS "Jul_Revenue",
SUM(IF (month = "Aug", revenue, null)) AS "Aug_Revenue",
SUM(IF (month = "Sep", revenue, null)) AS "Sep_Revenue",
SUM(IF (month = "Oct", revenue, null)) AS "Oct_Revenue",
SUM(IF (month = "Nov", revenue, null)) AS "Nov_Revenue",
SUM(IF (month = "Dec", revenue, null)) AS "Dec_Revenue" 
FROM Department 
GROUP BY id

# 方法2
SELECT a.id, 
    MAX((CASE WHEN a.month = 'Jan' THEN a.revenue ELSE NULL END)) as 'Jan_Revenue',
    MAX((CASE WHEN a.month = 'Feb' THEN a.revenue ELSE NULL END)) as 'Feb_Revenue',
    MAX((CASE WHEN a.month = 'Mar' THEN a.revenue ELSE NULL END)) as 'Mar_Revenue',
    MAX((CASE WHEN a.month = 'Apr' THEN a.revenue ELSE NULL END)) as 'Apr_Revenue',
    MAX((CASE WHEN a.month = 'May' THEN a.revenue ELSE NULL END)) as 'May_Revenue',
    MAX((CASE WHEN a.month = 'Jun' THEN a.revenue ELSE NULL END)) as 'Jun_Revenue',
    MAX((CASE WHEN a.month = 'Jul' THEN a.revenue ELSE NULL END)) as 'Jul_Revenue',
    MAX((CASE WHEN a.month = 'Aug' THEN a.revenue ELSE NULL END)) as 'Aug_Revenue',
    MAX((CASE WHEN a.month = 'Sep' THEN a.revenue ELSE NULL END)) as 'Sep_Revenue',
    MAX((CASE WHEN a.month = 'Oct' THEN a.revenue ELSE NULL END)) as 'Oct_Revenue',
    MAX((CASE WHEN a.month = 'Nov' THEN a.revenue ELSE NULL END)) as 'Nov_Revenue',
    MAX((CASE WHEN a.month = 'Dec' THEN a.revenue ELSE NULL END)) as 'Dec_Revenue'
FROM Department a GROUP BY a.id;

1193. 每月交易 I
这个题考察了date_format的使用方式

# 注意data_format函数的使用方式 0.4612
SELECT DATE_FORMAT(trans_date,'%Y-%m') AS month,  
       country,
       Count(*) as trans_count,
       Sum(IF(state='approved',1,0)) AS approved_count,
       Sum(amount) AS trans_total_amount,
       Sum(IF(state='approved',amount,0)) AS approved_total_amount    
FROM Transactions
GROUP BY DATE_FORMAT(trans_date,'%Y-%m'), country
ORDER BY month,country;