newline.sql

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