SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE IF NOT EXISTS `site` ( `site_id` INT(12) UNSIGNED NOT NULL, `url` VARCHAR(255) NOT NULL, `user_id` INT(12) UNSIGNED NOT NULL, `status` INT(12) UNSIGNED NOT NULL, PRIMARY KEY (`site_id`) ) DEFAULT CHARSET = utf8; INSERT INTO `site` (`site_id`, `url`, `user_id`, `status`) VALUES ('1025', 'site1.com', '275', 1), ('1026', 'test_site.net', '278', 0), ('1027', 'site17.net', '275', 1), ('1028', 'test_test_site.fr', '270', 1); CREATE TABLE IF NOT EXISTS `site_area` ( `site_area_id` INT(12) UNSIGNED NOT NULL, `name` VARCHAR(255) NOT NULL, `site_id` INT(12) UNSIGNED NOT NULL, `status` INT(12) UNSIGNED NOT NULL, `size` VARCHAR(255) NOT NULL, PRIMARY KEY (`site_area_id`) ) DEFAULT CHARSET = utf8; INSERT INTO `site_area` (`site_area_id`, `site_id`, `name`, `status`, `size`) VALUES ('628', '527', 'sidebar_1', 1, '160x600'), ('629', '1025', 'in_article', 1, '300x250'), ('673', '1025', 'sidebar_4', 1, '300x600'), ('674', '1025', 'sidebar_5', 1, '300x600'), ('676', '1025', 'sidebar_6', 1, '300x600'), ('677', '1025', 'sidebar_7', 0, '300x600'), ('670', '1026', 'sidebar_1', 1, '300x600'), ('671', '1026', 'sidebar_2', 0, '300x250'), ('675', '1026', 'sidebar_3', 0, '300x250'); CREATE TABLE IF NOT EXISTS `npm_site_area_stat_cache` ( `date` DATE NOT NULL, `site_area_id` INT(12) UNSIGNED NOT NULL, `impression_count` INT(12) UNSIGNED NOT NULL, `revenue` DECIMAL(21, 8), PRIMARY KEY (`date`, `site_area_id`) ) DEFAULT CHARSET = utf8; INSERT INTO `npm_site_area_stat_cache` (`date`, `site_area_id`, `impression_count`, `revenue`) VALUES ('2019-10-20', 628, 57565, 25.10), ('2019-10-22', 628, 61277, 40.25), ('2019-10-22', 629, 1245, 0.95), ('2019-10-22', 673, 755, 0.05), ('2019-10-22', 651, 889115, 525.25); SELECT Site_id, url, COUNT(DISTINCT site_area.site_area_id) active_blocks, SUM(impression_count) impression_count, SUM(revenue) revenue FROM site JOIN site_area USING(site_id) LEFT JOIN npm_site_area_stat_cache USING(site_area_id) WHERE site_area.status = 1 GROUP BY site_id, url HAVING COUNT(DISTINCT site_area.site_area_id) > 3;