Skip to content

Instantly share code, notes, and snippets.

@ufo22940268
Created April 26, 2018 09:59
Show Gist options
  • Save ufo22940268/7aeb2e7591b83a48a40e42bc7411e869 to your computer and use it in GitHub Desktop.
Save ufo22940268/7aeb2e7591b83a48a40e42bc7411e869 to your computer and use it in GitHub Desktop.
SELECT date,id,name,if(defalutinterest_num is null,0,defalutinterest_num),
if(one_unsubscribe_num is null,0,one_unsubscribe_num),
if(three_unsubscribe_sum is null,0,three_unsubscribe_sum),
if(seven_unsubscribe_sum is null,0,seven_unsubscribe_sum),
if(avg_quguan_rate is null,0,avg_quguan_rate),
if(next_active_rate is null,0,next_active_rate),
if(valid_num is null,0,valid_num),
if(valid_num /defalutinterest_num is null,0,valid_num /defalutinterest_num),
if(nextread_num is null,0,nextread_num),
if(nextread_num/valid_num is null,0,nextread_num/valid_num),
if(interest_num is null,0,interest_num),
if(interest_sum is null,0,interest_sum),
if(sumtotalprice is null,0,sumtotalprice),
if(dan_num is null,0,dan_num),
if(sumtotalprice/dan_num is null,0,sumtotalprice/dan_num),
if(ke_num is null,0,ke_num),
if(sumtotalprice/ke_num is null,0,sumtotalprice/ke_num),
if(newdefault_sumtotalprice is null,0,newdefault_sumtotalprice),
if(newdefault_dan_num is null,0,newdefault_dan_num),
if(newdefault_sumtotalprice/newdefault_dan_num is null,0,newdefault_sumtotalprice/newdefault_dan_num),
if(newdefault_ke_num is null,0,newdefault_ke_num),
if(newdefault_sumtotalprice/newdefault_ke_num is null,0,newdefault_sumtotalprice/newdefault_ke_num)
FROM
(
SELECT id,name,date
FROM
(SELECT id,name
FROM channels
WHERE launched = true
)
CROSS JOIN
(SELECT date(time) as date
FROM orders
WHERE date(time) = date'2018-04-20'
GROUP BY 1
)
)aaa
LEFT JOIN
( SELECT a.channel,count(a.user) as defalutinterest_num,
count(b.user) as one_unsubscribe_num,
count(c.user) as three_unsubscribe_sum,
count(d.user) as seven_unsubscribe_sum
FROM
(SELECT channel,user
FROM userlogs
WHERE date = date'2018-04-20'
AND batchsubscribe = true
AND action = 'CHANNEL_SUBSCRIBE'
GROUP BY 1,2)a
LEFT JOIN
(SELECT user,channel
FROM userlogs
WHERE date = date'2018-04-20'
AND action = 'CHANNEL_UNSUBSCRIBE'
GROUP BY 1,2)b
ON a.user = b.user AND a.channel = b.channel
LEFT JOIN
(SELECT user,channel
FROM userlogs
WHERE date between date'2018-04-20' AND date'2018-04-22'
AND action = 'CHANNEL_UNSUBSCRIBE'
GROUP BY 1,2)c
ON a.user = c.user AND a.channel = c.channel
LEFT JOIN
(SELECT user,channel
FROM userlogs
WHERE date between date'2018-04-20' AND date'2018-04-26'
AND action = 'CHANNEL_UNSUBSCRIBE'
GROUP BY 1,2)d
ON a.user = d.user AND a.channel = d.channel
GROUP BY 1
)bbb
ON aaa.id = bbb.channel
LEFT JOIN
(
SELECT a.channel,count(b.user) as valid_num ,count(c.user) as nextread_num
FROM
(SELECT user,channel
FROM userlogs
WHERE date = date'2018-04-20'
AND batchsubscribe = true
AND action = 'CHANNEL_SUBSCRIBE'
GROUP BY 1,2)a
JOIN
(SELECT user,channel
FROM userlogs
WHERE date = date'2018-04-20'
AND action = 'REVIEW_ENTER'
GROUP BY 1,2)b
ON a.user =b.user AND a.channel = b.channel
LEFT JOIN
(SELECT user,channel
FROM userlogs
WHERE date = date'2018-04-21'
AND action = 'REVIEW_ENTER'
GROUP BY 1,2)c
ON a.user = c.user and a.channel = c.channel
GROUP BY 1
)ccc
ON aaa.id = ccc.channel
LEFT JOIN
(SELECT channel,count(distinct user) as interest_num
FROM userlogs
WHERE date = date'2018-04-20'
AND action = 'CHANNEL_SUBSCRIBE'
GROUP BY 1)ddd
ON aaa.id = ddd.channel
LEFT JOIN
(SELECT channel,sum(`subscribeÇount`) as interest_sum
FROM channel_subscribe_history
WHERE date = date'2018-04-20'
GROUP BY 1)eee
ON aaa.id = eee.channel
LEFT JOIN
(SELECT channel,sum(totalprice) as sumtotalprice,
count(*) as dan_num ,count(distinct buyer) as ke_num
FROM orders
WHERE paid = true
AND date(time) = date'2018-04-20'
GROUP BY 1)fff
ON aaa.id = fff.channel
LEFT JOIN
(SELECT b.channel,sum(totalprice) as newdefault_sumtotalprice,
count(*) as newdefault_dan_num ,count(distinct buyer) as newdefault_ke_num
FROM
(SELECT id
FROM users
WHERE date(time) = date'2018-04-20'
AND role is NULL)a
JOIN
(SELECT id,buyer,totalprice,channel
FROM orders
WHERE paid = true
AND date(time) = date'2018-04-20')b
ON a.id = b.buyer
JOIN
(SELECT user,channel
FROM userlogs
WHERE date = date'2018-04-20'
AND batchsubscribe = true
AND action = 'CHANNEL_SUBSCRIBE'
GROUP BY 1,2)c
ON b.buyer = c.user AND b.channel = c.channel
GROUP BY 1)hhh
ON aaa.id = hhh.channel
LEFT JOIN
(SELECT channel,sum(quguan)/6 as avg_quguan_rate
FROM
(SELECT aa.date,aa.channel,unsubscribe_num/liucun_num as quguan
FROM
(SELECT date,a.channel,count(a.user) as unsubscribe_num
FROM
(SELECT user,channel
FROM userlogs
WHERE date = date'2018-04-20'
AND batchsubscribe = true
AND action = 'CHANNEL_SUBSCRIBE'
GROUP BY 1,2)a
JOIN
(SELECT date,user,channel
FROM userlogs
WHERE date between date'2018-04-21' and date'2018-04-26'
AND action = 'CHANNEL_UNSUBSCRIBE'
GROUP BY 1,2,3)b
ON a.user = b.user and a.channel= b.channel
GROUP BY 1,2)aa
JOIN
(SELECT date,a.channel,count(distinct b.user) as liucun_num
FROM
(SELECT user,channel
FROM userlogs
WHERE date = date'2018-04-20'
AND batchsubscribe = true
AND action = 'CHANNEL_SUBSCRIBE'
GROUP BY 1,2)a
JOIN
(SELECT date,user,channel
FROM userlogs
WHERE date between date'2018-04-21' and date'2018-04-26'
GROUP BY 1,2,3)b
ON a.user = b.user
GROUP BY 1,2)bb
ON aa.date = bb.date AND aa.channel =bb.channel)aaa
GROUP BY 1)aaaa
ON aaa.id = aaaa.channel
LEFT JOIN
(SELECT a.channel,count(a.user) as today_num,count(b.user) as next_active_num,
count(b.user) / count(a.user) as next_active_rate
FROM
(SELECT user,channel
FROM userlogs
WHERE date = date'2018-04-20'
AND batchsubscribe = true
AND action = 'CHANNEL_SUBSCRIBE'
GROUP BY 1,2)a
LEFT JOIN
(SELECT user
FROM userlogs
WHERE date = date'2018-04-21'
GROUP BY 1)b
ON a.user = b.user
GROUP BY 1)bbbb
ON aaa.id =bbbb.channel
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment