FB onsite 关于SQL window function的使用

请问楼主 一个用户一天会有多个status吗

一点思路：

select Date, count(distinct t2.Account)/count(distinct t1.account)
from table t1
left join (select * from table where Status = 'Fraud') t2
on t1.account = t2.account
where t1.Spent > 0
group by Date
order by Date ASC;

select count(distinct Account) as numoffirstfraud
from table
where date = getdate()
and Status = 'Fraud'
and Account not in (
select Account
from table
where Status = 'Fraud'
and date <> dateadd(day,1,getdate())
)

补充内容 (2019-3-8 11:02):
dateadd里应该是-1，打错

lydia31358 发表于 2019-2-28 10:09
我觉得row_number() over(partition by account order by date desc) as rk 这里面不应该有desc。。

+1 同意 因为加了desc是说最后一次mark成fraud是今天

onsite就是这道题被给了一个差评，面试官给的方法是，今天fraud account number 减去昨天 fraud account number. 别用window function, 也别今天join 昨天，后者有个edge case就是，今天open and 今天fraud的就算不到。 fb sql题有时真一言难尽

Q1:
with act
as
(
select
date,
iif(spend>0, count(distinct account),0) as total_act_user
from #fb
group by
date,
spend

),fraud
as
(
select
date,
iif(status = 'Fraud', count(distinct account),0) as total_fraud_user
from #fb
group by
date,
status)
. ----select
a.date,
iif(a.total_act_user = 0,0, cast (cast(f.total_fraud_user as float)/a.total_act_user as float)) as Percentage
from act a
left outer join fraud f on a.date= f.date

料理 发表于 2019-3-4 01:39
Select date, sum(if(usertype=fraud and spend>0,1,0)) /sum(if(spend>0).1,0) as percent group by date. ...

感觉你这个有点问题的点在于如果一个user每天fraud了两次，且spend都>0，你这个就重复count了

Q1
Select date,
count(case when status=’Fraud’ then distinct account else NULL end) /
Count(distinct account) as percentage
From table
Where spend > 0
Group by date

Q2
Select count(distinct account)
From
(Select account, min(date) as first_fraud
From table
Where status = ‘Fraud’
Group by account)
As tmp
Where tmp. first_fraud = getdate();
第二种方法：
Select count(distinct account)
From table
Where status = ‘Fraud’
And account not in (
Select account
From table
Where status = ‘Fraud’ and date < getdate();

第二问没有必要这么复杂
select count(*)
from (
select account,
         min(date)
from account_id
where status = 'Fraud'
having min(date) = current()
)

求问楼主概率题面的是什么呀