with a1 as (
select user1 as user_id,
user2 as friend_id
from friend_relationships
union
select user2 as user_id,
user1 as friend_id
from friend_relationships
),
a2 as (
select a1.user_id,
friend_id,
steps
from a1
join user_steps u
on a1.friend_id = u.user_id
),
a3 as (
select user_id,
user_id as friend_id,
steps
from user_steps
union
select user_id,
friend_id,
steps
from a2
),
a4 as (
select user_id,
friend_id,
steps,
rank() over (partition by user_id order by steps desc) as row_num
from a3
)
select user_id,
row_num
from a4
where user_id = friend_id
执行得到的结果相同,为什么说我错了呀
with At as (
select distinct usr_id,
date(login_time) as login_date
from user_login_log
where datediff(curdate(), login_time) <= 30
)
select At.login_date,
concat(round(count(distinct Bt.usr_id)/count(distinct At.usr_id)*100, 2), '%') as T1_retention_rate
from At
left join At Bt on At.usr_id = Bt.usr_id
and datediff(Bt.login_date, At.login_date) = 1
group by At.login_date
with new1 as (
select *,
date(login_time) as login_date
from user_login_log
where datediff(curdate(), date(login_time)) <= 180
),
new2 as (
select usr_id,
count(distinct login_date) as freq
from new1
group by usr_id
),
new3 as (
select *,
if(freq between 1 and 5, 1,
if(freq between 6 and 10, 2,
if(freq between 11 and 20, 3, 4))) as type
from new2
)
select sum(type = 1) as days_1_to_5,
sum(type = 2) as days_6_to_10,
sum(type = 3) as days_11_to_20,
sum(type = 4) as days_over_20
from new3
select lpad(hour_entered, 2, '0') as hour_entered, enter_count
from(
select hour(enter_time) as hour_entered,
count(1) as enter_count
from ks_live_t1 t1 join ks_live_t2 t2 on t1.live_id = t2.live_id
group by hour(enter_time)) as new_table
order by hour_entered asc这样才对吧
select t1.live_id,
live_nm,
count(distinct case when '2021-09-12 23:48:38' between enter_time and leave_time then usr_id else null end) as online_users
from ks_live_t1 t1
join ks_live_t2 t2
on t1.live_id = t2.live_id
group by t1.live_id, live_nm
having online_users > 0
order by online_users desc
select t1.live_id,
live_nm,
count(case when '2021-09-12 23:48:38' between enter_time and leave_time then usr_id else null end) as online_users
from ks_live_t1 t1
join ks_live_t2 t2
on t1.live_id = t2.live_id
group by t1.live_id, live_nm
order by online_users desc
select t1.live_id,
live_nm,
count(distinct case when '2021-09-12 23:48:38' between enter_time and leave_time then usr_id else null end) as online_users
from ks_live_t1 t1
join ks_live_t2 t2
on t1.live_id = t2.live_id
group by t1.live_id, live_nm
order by online_users desc
select t1.live_id,
live_nm,
count(case when '2021-09-12 11:48:38' between enter_time and leave_time then usr_id else null end) as online_users
from ks_live_t1 t1
join ks_live_t2 t2
on t1.live_id = t2.live_id
group by t1.live_id, live_nm
order by online_users desc
WITH OnlineUsers AS (
SELECT
t1.live_id,
t1.usr_id
FROM
ks_live_t1 t1
WHERE
'2021-09-12 23:48:38' BETWEEN t1.leave_time AND t1.enter_time
)
SELECT
ou.live_id,
t2.live_nm,
COUNT(distinct ou.usr_id) AS online_users
FROM
OnlineUsers ou
JOIN
ks_live_t2 t2
ON
ou.live_id = t2.live_id
GROUP BY
ou.live_id, t2.live_nm
ORDER BY
online_users DESC
select t1.live_id,
live_nm,
count(distinct case when '2021-09-12 11:48:38' between enter_time and leave_time then usr_id else null end) as online_users
from ks_live_t1 t1
join ks_live_t2 t2
on t1.live_id = t2.live_id
group by t1.live_id, live_nm
order by online_users desc
select t1.live_id,
live_nm,
count(case when '2021-09-12 11:48:38' between enter_time and leave_time
then 1 else null end) as online_users
from ks_live_t1 t1
join ks_live_t2 t2
on t1.live_id = t2.live_id
group by t1.live_id, live_nm
order by online_users desc
with a1 as (
select user1 as user_id,
user2 as friend_id,
steps
from friend_relationships f
join user_steps u
on f.user2 = u.user_id
union
select user2 as user_id,
user1 as friend_id,
steps
from friend_relationships f
join user_steps u
on f.user1 = u.user_id
union
select user_id,
user_id as friend_id,
steps
from user_steps
),
a2 as (
select user_id,
friend_id,
steps,
row_number() over (partition by user_id order by steps desc) as row_num
from a1
)
select user_id,
row_num
from a2
where user_id = friend_id
with a1 as (
select user1 as user_id,
user2 as friend_id,
steps
from friend_relationships f
join user_steps u
on f.user2 = u.user_id
union
select user2 as user_id,
user1 as friend_id,
steps
from friend_relationships f
join user_steps u
on f.user1 = u.user_id
union
select user_id,
user_id as friend_id,
steps
from user_steps
),
a2 as (
select user_id,
friend_id,
steps,
rank() over (partition by user_id order by steps desc) as row_num
from a1
)
select user_id,
row_num
from a2
where user_id = friend_id
with friend_steps as (
select t1.user1 as user_id,
t1.user2 as friend_id,
t2.steps
from friend_relationships t1
join user_steps t2 on t1.user2 = t2.user_id
union
select t1.user2 as user_id,
t1.user1 as friend_id,
t2.steps
from friend_relationships t1
join user_steps t2 on t1.user1 = t2.user_id
union
select user_id,
user_id as friend_id,
steps
from user_steps
),
ranked_steps as (
select fs.user_id,
fs.friend_id,
fs.steps,
row_number() over (partition by fs.user_id order by fs.steps desc) as row_num
from friend_steps fs
)
select rs.user_id,
rs.row_num
from ranked_steps rs
where rs.user_id = rs.friend_id;
with a1 as (
select user1 as user_id,
user2 as friend_id,
steps
from friend_relationships f
join user_steps u
on f.user2 = u.user_id
union
select user2 as user_id,
user1 as friend_id,
steps
from friend_relationships f
join user_steps u
on f.user1 = u.user_id
),
a2 as (
select a1.user_id,
friend_id,
steps
from a1
union
select user_id,
user_id as friend_id,
steps
from user_steps
),
a3 as (
select user_id,
friend_id,
steps,
rank() over (partition by user_id order by steps desc) as row_num
from a2
)
select user_id,
row_num
from a3
where user_id = friend_id
with a1 as (
select user1 as user_id,
user2 as friend_id
from friend_relationships
union
select user2 as user_id,
user1 as friend_id
from friend_relationships
),
a2 as (
select a1.user_id,
friend_id,
steps
from a1
join user_steps u
on a1.friend_id = u.user_id
union
select user_id,
user_id as friend_id,
steps
from user_steps
),
a3 as (
select user_id,
friend_id,
steps,
rank() over (partition by user_id order by steps desc) as row_num
from a2
)
select user_id,
row_num
from a3
where user_id = friend_id
with a1 as (
select user1 as user_id,
user2 as friend_id
from friend_relationships
union
select user2 as user_id,
user1 as friend_id
from friend_relationships
),
a2 as (
select a1.user_id,
friend_id,
steps
from a1
join user_steps u
on a1.friend_id = u.user_id
),
a3 as (
select user_id,
user_id as friend_id,
steps
from user_steps
union
select user_id,
friend_id,
steps
from a2
),
a4 as (
select user_id,
friend_id,
steps,
rank() over (partition by user_id order by steps desc) as row_num
from a3
)
select user_id,
row_num
from a4
where user_id = friend_id
with a1 as (
select user1 as user_id,
user2 as friend_id
from friend_relationships
union
select user2 as user_id,
user1 as friend_id
from friend_relationships
),
a2 as (
select a1.user_id,
friend_id,
steps
from a1
join user_steps u
on a1.friend_id = u.user_id
),
a3 as (
select user_id,
friend_id,
steps
from a2
union
select user_id,
user_id as friend_id,
steps
from user_steps
),
a4 as (
select user_id,
friend_id,
steps,
row_number() over (partition by user_id order by steps desc) as row_num
from a3
)
select user_id,
row_num
from a4
where user_id = friend_id
with a1 as (
select t1.user1 as user_id,
t1.user2 as friend_id,
t2.steps
from friend_relationships t1
join user_steps t2 on t1.user2 = t2.user_id
union
select t1.user2 as user_id,
t1.user1 as friend_id,
t2.steps
from friend_relationships t1
join user_steps t2 on t1.user1 = t2.user_id
),
a2 as (
select user_id,
friend_id,
steps
from a1
union
select user_id,
user_id as friend_id,
steps
from user_steps
),
a3 as (
select user_id,
friend_id,
steps,
row_number() over (partition by user_id order by steps desc) as row_num
from a2
)
select user_id,
row_num
from a3
where user_id = friend_id
with a1 as (
select t1.user1 as user_id,
t1.user2 as friend_id,
t2.steps
from friend_relationships t1
join user_steps t2 on t1.user2 = t2.user_id
union
select t1.user2 as user_id,
t1.user1 as friend_id,
t2.steps
from friend_relationships t1
join user_steps t2 on t1.user1 = t2.user_id
union
select user_id,
user_id as friend_id,
steps
from user_steps
),
a2 as (
select user_id,
friend_id,
steps,
row_number() over (partition by user_id order by steps desc) as row_num
from a1
)
select user_id,
row_num
from a2
where user_id = friend_id
with a1 as (
select t1.user1 as user_id,
t1.user2 as friend_id,
t2.steps
from friend_relationships t1
join user_steps t2 on t1.user2 = t2.user_id
union
select t1.user2 as user_id,
t1.user1 as friend_id,
t2.steps
from friend_relationships t1
join user_steps t2 on t1.user1 = t2.user_id
),
a2 as (
select user_id,
user_id as friend_id,
steps
from user_steps
union
select user_id,
friend_id,
steps
from a1
),
a3 as (
select user_id,
friend_id,
steps,
row_number() over (partition by user_id order by steps desc) as row_num
from a2
)
select user_id,
row_num
from a3
where user_id = friend_id
with a1 as (
select t1.user1 as user_id,
t1.user2 as friend_id,
t2.steps
from friend_relationships t1
join user_steps t2 on t1.user2 = t2.user_id
union
select t1.user2 as user_id,
t1.user1 as friend_id,
t2.steps
from friend_relationships t1
join user_steps t2 on t1.user1 = t2.user_id
),
a2 as (
select user_id,
user_id as friend_id,
steps
from user_steps
union
select user_id,
friend_id,
steps
from a1
),
a3 as (
select user_id,
friend_id,
steps,
rank() over (partition by user_id order by steps desc) as row_num
from a2
)
select user_id,
row_num
from a3
where user_id = friend_id
with a1 as (
select t1.user1 as user_id,
t1.user2 as friend_id,
t2.steps
from friend_relationships t1
join user_steps t2 on t1.user2 = t2.user_id
union
select t1.user2 as user_id,
t1.user1 as friend_id,
t2.steps
from friend_relationships t1
join user_steps t2 on t1.user1 = t2.user_id
union
select user_id,
user_id as friend_id,
steps
from user_steps
),
a2 as (
select user_id,
user_id as friend_id,
steps
from user_steps
union
select user_id,
friend_id,
steps
from a1
),
a3 as (
select user_id,
friend_id,
steps,
rank() over (partition by user_id order by steps desc) as row_num
from a2
)
select user_id,
row_num
from a3
where user_id = friend_id
with a1 as (
select user1 as user_id,
user2 as friend_id,
steps
from friend_relationships t1
join user_steps t2 on t1.user2 = t2.user_id
union
select user2 as user_id,
user1 as friend_id,
steps
from friend_relationships t1
join user_steps t2 on t1.user1 = t2.user_id
),
a2 as (
select user_id,
user_id as friend_id,
steps
from user_steps
union
select user_id,
friend_id,
steps
from a1
),
a3 as (
select user_id,
friend_id,
steps,
rank() over (partition by user_id order by steps desc) as row_num
from a2
)
select user_id,
row_num
from a3
where user_id = friend_id