sql 写法求解

[helloworld000]

看到一个面试题想请假一下

two tables:

friending (sender_id, receiver_id, send_date, accept_date, sender_country)

age (user_id, age_group)

1.1 How many friend-requests sent on each day of the week for the last 4 weeks?

1.2 What is the average number of friend-requests sent per user over the last past week by age group? // 前 7 天内每个 age group 的平均好友请求数

第 1 个是直接 select sender_id 然后 where send_data < 28 吧？

主要是想请问一下第 2 个怎么写的？ 这 2 个 tables union，然后 COUNT(sender_id) OVER (PARTITION BY age_group) 这种？

不好意思 sql 不太懂临时抱佛脚学的比较乱七八糟，希望指教

[hand515]

Join

[a87965028]

select a.age_group, count(1) / count(distinct a.user_id) as avg
from friending as f
inner join age as a on f.sender_id = a.user_id
where send_date < datediff(day, -7, getdate())
group by age_group