2019.12.6 LEETCODE SQL

1149 Article Views 2

SELECT DISTINCT viewer_id AS id
FROM Views a
GROUP BY a.view_date, viewer_id
HAVING COUNT(DISTINCT article_id) >= 2
ORDER BY viewer_id

1158 Market Analysis

想要显示 0 就要加 sum(if(,1,0))

Select o.buyer_id,u.join_date,SUM(IF(YEAR(o.order_date)='2019',1,0)) as orders_in_2019
From Orders o 
Join Users u
on o.buyer_id = u.user_id  
Group by o.buyer_id 
ORDER BY o.buyer_id

1164 Product Price at a Given Date

三天 select * from T_news where datediff(day,addtime,getdate())<= 2 and datediff(day,addtime,getdate())>= 0
一周 select * from T_news WHERE (DATEPART(wk, addtime) = DATEPART(wk, GETDATE())) AND (DATEPART(yy, addtime) = DATEPART(yy, GETDATE()))
注意：此时不能用 datediff 差值为7,因为，datediff只表示间隔数
一月 select * from T_news WHERE (DATEPART(yy, addtime) = DATEPART(yy, GETDATE())) AND (DATEPART(mm, addtime) = DATEPART(mm, GETDATE()))
一季度 select * from T_news where DATEPART(qq, addtime) = DATEPART(qq, GETDATE()) and DATEPART(yy, addtime) = DATEPART(yy, GETDATE())
本周：select * from table where datediff(week,C_CALLTIME,getdate())=0 --C_CALLTIME 为日期字段
本月：select * from table where datediff(Month,C_CALLTIME,getdate())=0 --C_CALLTIME 为日期字段
本季：select * from table where datediff(qq,C_CALLTIME,getdate())=0
前半年1-6，后半年7-12：select * from table where datepart(mm,C_CALLTIME)/7 = datepart(mm,getdate())/7

自己写的好难受过不了…

Select p.product_id, new_price as price 
From Products p
WHERE p.change_date <= '2019-08-16'
Group by p.product_id
Having min(datediff(p.change_date,'2019-08-16'))
union 
Select product_id not in 
(
    Select distinct p.product_id
    From Products p
    WHERE p.change_date <= '2019-08-16'

) ,10 as price 
From Products

我要举报！！！这里有BUG !!!
为什么别人的MAX(change_date)好用，我的就不好用！！！为什么！！！

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

 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')

1174 Immediate Food Delievery

通过的猝不及防

SELECT ROUND( (
    SELECT COUNT(*)
    FROM (
        SELECT customer_id,min(order_date) d1,min(customer_pref_delivery_date) d2
        From Delivery 
        Group by customer_id) s 
        Where s.d1 = s.d2) *100 / COUNT(*) ,2)AS immediate_percentage 
FROM(
SELECT customer_id,min(order_date) d1,min(customer_pref_delivery_date) d2
From Delivery 
Group by customer_id)  as s 

1193 每日交易

注意这里可以用date_format(x,’%Y%m’),country
来分组讨论
类似于重采样

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 t
group by
date_format(trans_date,'%Y-%m'),country