Advertisement
adzhanihani

(Real) ALL CLICKS DATA - PGC ONLY (BUZZ UP)

Feb 25th, 2018
90
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 0.92 KB | None | 0 0
  1. SELECT
  2.     c.url_source as url_source,
  3.     COUNT(DISTINCT g.gid) as gids,
  4.     case c.mp_id when 54 then 'Y' else 'F' end as is_pgc,
  5.     SUM(g.pan_click_count) AS click,
  6.     SUM(g.client_impression_count) AS impression,
  7.     SUM(g.pan_click_count) / SUM(g.client_impression_count) * 100 AS ctr,
  8.     SUM(g.share_count) AS share,
  9.     SUM(g.digg_count) AS digg,
  10.     SUM(g.bury_count) AS bury,
  11.     SUM(g.favorite_count) AS favorite
  12. FROM
  13.     i18n_pgc_dw_sg.log_v3_item_stats g
  14.     LEFT JOIN `default`.crawl_group_stats_all c ON g.gid = c.gid
  15. WHERE
  16.     (
  17.         g.`date` BETWEEN '20180129' AND '20180204'
  18.     )
  19.     AND g.app_id = 1194
  20.     AND c.language = 'id'
  21.     AND g.media_id IN (6508338496992707595,6512342983285670924,6512718671457027084,6512718876269085707,6512722248049099787,6514167137232225291,6514524053460485132,6515283107216622603)
  22.     AND c.mp_id = 54
  23. GROUP BY c.url_source, c.mp_id
  24. ORDER BY click DESC
  25. LIMIT 20
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement