狗家 Product Analyst 电面

谢谢楼主分享！

谢谢楼主分享～

楼主可以试一下用running tool?

select date, user_id
from
(
select *,
sum(views) over(order by date rows between 7 preceding to current row) as cum_sum
from tbl) as t
where cum_sum >0

第二个SQL不需要这么复杂。你可以create 一个 date table
with date_table as (
select distinct date
from youtube_table
order by date
)
select date_table.date,
         count(distinct user_id)
from date_table
inner join youtube_table
on youtube_table.date BETWEEN  date_table.date AND date_table.date - '7  days' :: INTERVAL

Select a.user_id,a.date,count(distinct(b.date)) as num_days_views from table a
Left join (select * from table where view>0)b
On a.user_id=b.user_id
And b.date between a.date-8 and a.date-1
Group by 1,2
Having num_days_views=7

想问一下楼主是先填的questionaie吗

感谢楼主的分享！

maohappy2013 发表于 2019-9-10 04:50
第二个SQL不需要这么复杂。你可以create 一个 date table
with date_table as (
select distinct date

我也赞同你的做法。唯一的一点是我觉得要再加一个where限制view是大于0的

select date_table.date,
         count(distinct user_id)
from date_table
-baidu 1point3acresinner join youtube_table
on youtube_table.date BETWEEN  date_table.date AND date_table.date - '7  days' :: INTERVAL
where youtube_table.view > 0  -- add a where condition
group by date_table.date  -- add group by

good sharing

. 1point 3acres 这题在考lag window function. 面过这么多间只有看过G家考lag window function

create table Employee
(
date date,
user_id int,
view int
);
  
insert into Employee (date, user_id, view)
VALUES
('01-01-2020','1','1'),
('01-02-2020','1','0'),
('01-03-2020','1','0'),
('01-04-2020','1','1'),
('01-05-2020','1','1'),
('01-06-2020','1','1'),
('01-07-2020','1','1'),
('01-08-2020','1','1'),
('01-01-2020','2','0'),
('01-02-2020','2','0'),
('01-03-2020','2','0'),
('01-04-2020','2','0'),
('01-05-2020','2','0'),
('01-06-2020','2','0'),
('01-07-2020','2','0')
;



SELECT
date,
user_id,
lag(view,7) over(partition by user_id order by date asc) as last_7_day_view
From Employee

output:

date        user_id        last_7_day_view
2020-01-01        1        (null)
2020-01-02        1        (null)
2020-01-03        1        (null)
2020-01-04        1        (null)
2020-01-05        1        (null)
2020-01-06        1        (null)
2020-01-07        1        (null)
2020-01-08        1        1
2020-01-01        2        (null)
2020-01-02        2        (null)
2020-01-03        2        (null)
2020-01-04        2        (null)
2020-01-05        2        (null)
2020-01-06        2        (null)
2020-01-07        2        (null)