ztingHhB

1DROP temporary TABLE IF EXISTS t1;
2
3CREATE temporary TABLE t1 AS
4  SELECT s.site_id,
5         sd24.sa_user_id,
6         sd24.site_area_id,
7         s.url
8  FROM   npm_site_area_stat_cache npm
9         JOIN site_area_design_24 sd24
10           ON sd24.site_area_id = npm.site_area_id
11         JOIN site s
12           ON s.site_id = sd24.sa_site_id
13         JOIN site_area sa
14           ON sa.site_area_id = sd24.site_area_id
15         JOIN site_demand_source_status sss
16           ON sss.site_id = s.site_id
17  WHERE  sd24.npm_size_id NOT IN ( 40, 41 )
18         AND npm.site_area_id NOT IN (SELECT site_area_id
19                                      FROM   attached_site_areas)
20         AND npm.site_area_id NOT IN (SELECT attached_site_area_id
21                                      FROM   attached_site_areas)
22         AND npm.external_view_count > 10
23         AND sa.name NOT LIKE "%adfox%"
24         AND sa.render_backup_over = "0"
25         AND sss.demand_source_id = 3
26         AND sss.status = "active"
27  GROUP  BY npm.site_id,
28            npm.site_area_id;
29
30SELECT t1.sa_user_id,
31       t1.site_id,
32       t1.url,
33       request_count,
34       GROUP_CONCAT(DISTINCT t1.site_area_id SEPARATOR ',')
35FROM   headerbidding_request_site_stat hrss
36       JOIN t1
37         ON hrss.site_id = t1.site_id
38WHERE  hrss.`date` = '2018-07-12'
39       AND hrss.`request_count` > 10
40GROUP  BY t1.site_id
41ORDER  BY request_count DESC;