API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jul 11th, 2016
197
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 8.68 KB | None | 0 0
raw download clone embed print report
  1. 0.0003     
  2. CREATE TEMPORARY TABLE IF NOT EXISTS `generic` (
  3.     `date` DATE NOT NULL,
  4.     `partner_id` INT(11) NOT NULL,
  5.     PRIMARY KEY(`date`, `partner_id`)
  6. ) ENGINE=MEMORY;
  7.  
  8. 0.0003      INSERT INTO `generic` (`date`, `partner_id`) VALUES ('2015-07-04','13'), ('2015-07-05','13'), ('2015-07-06','13'), ('2015-07-07','13'), ('2015-07-08','13'), ('2015-07-09','13'), ('2015-07-10','13'), ('2015-07-11','13'), ('2015-07-12','13'), ('2015-07-13','13'), ('2015-07-14','13'), ('2015-07-15','13'), ('2015-07-16','13'), ('2015-07-17','13'), ('2015-07-18','13'), ('2015-07-19','13'), ('2015-07-20','13'), ('2015-07-21','13'), ('2015-07-22','13'), ('2015-07-23','13'), ('2015-07-24','13'), ('2015-07-25','13'), ('2015-07-26','13'), ('2015-07-27','13'), ('2015-07-28','13'), ('2015-07-29','13'), ('2015-07-30','13'), ('2015-07-31','13'), ('2015-08-01','13'), ('2015-08-02','13'), ('2015-08-03','13'), ('2015-08-04','13'), ('2015-08-05','13'), ('2015-08-06','13'), ('2015-08-07','13'), ('2015-08-08','13'), ('2015-08-09','13'), ('2015-08-10','13'), ('2015-08-11','13'), ('2015-08-12','13'), ('2015-08-13','13'), ('2015-08-14','13'), ('2015-08-15','13'), ('2015-08-16','13'), ('2015-08-17','13'), ('2015-08-18','13'), ('2015-08-19','13'), ('2015-08-20','13'), ('2015-08-21','13'), ('2015-08-22','13'), ('2015-08-23','13'), ('2015-08-24','13'), ('2015-08-25','13'), ('2015-08-26','13'), ('2015-08-27','13'), ('2015-08-28','13'), ('2015-08-29','13'), ('2015-08-30','13'), ('2015-08-31','13'), ('2015-09-01','13'), ('2015-09-02','13'), ('2015-09-03','13'), ('2015-09-04','13'), ('2015-09-05','13'), ('2015-09-06','13'), ('2015-09-07','13'), ('2015-09-08','13'), ('2015-09-09','13'), ('2015-09-10','13'), ('2015-09-11','13'), ('2015-09-12','13'), ('2015-09-13','13'), ('2015-09-14','13'), ('2015-09-15','13'), ('2015-09-16','13'), ('2015-09-17','13'), ('2015-09-18','13'), ('2015-09-19','13'), ('2015-09-20','13'), ('2015-09-21','13'), ('2015-09-22','13'), ('2015-09-23','13'), ('2015-09-24','13'), ('2015-09-25','13'), ('2015-09-26','13'), ('2015-09-27','13'), ('2015-09-28','13'), ('2015-09-29','13'), ('2015-09-30','13'), ('2015-10-01','13'), ('2015-10-02','13'), ('2015-10-03','13'), ('2015-10-04','13'), ('2015-10-05','13'), ('2015-10-06','13'), ('2015-10-07','13'), ('2015-10-08','13'), ('2015-10-09','13'), ('2015-10-10','13'), ('2015-10-11','13')
  9. 0.0004      INSERT INTO `generic` (`date`, `partner_id`) VALUES ('2015-10-12','13'), ('2015-10-13','13'), ('2015-10-14','13'), ('2015-10-15','13'), ('2015-10-16','13'), ('2015-10-17','13'), ('2015-10-18','13'), ('2015-10-19','13'), ('2015-10-20','13'), ('2015-10-21','13'), ('2015-10-22','13'), ('2015-10-23','13'), ('2015-10-24','13'), ('2015-10-25','13'), ('2015-10-26','13'), ('2015-10-27','13'), ('2015-10-28','13'), ('2015-10-29','13'), ('2015-10-30','13'), ('2015-10-31','13'), ('2015-11-01','13'), ('2015-11-02','13'), ('2015-11-03','13'), ('2015-11-04','13'), ('2015-11-05','13'), ('2015-11-06','13'), ('2015-11-07','13'), ('2015-11-08','13'), ('2015-11-09','13'), ('2015-11-10','13'), ('2015-11-11','13'), ('2015-11-12','13'), ('2015-11-13','13'), ('2015-11-14','13'), ('2015-11-15','13'), ('2015-11-16','13'), ('2015-11-17','13'), ('2015-11-18','13'), ('2015-11-19','13'), ('2015-11-20','13'), ('2015-11-21','13'), ('2015-11-22','13'), ('2015-11-23','13'), ('2015-11-24','13'), ('2015-11-25','13'), ('2015-11-26','13'), ('2015-11-27','13'), ('2015-11-28','13'), ('2015-11-29','13'), ('2015-11-30','13'), ('2015-12-01','13'), ('2015-12-02','13'), ('2015-12-03','13'), ('2015-12-04','13'), ('2015-12-05','13'), ('2015-12-06','13'), ('2015-12-07','13'), ('2015-12-08','13'), ('2015-12-09','13'), ('2015-12-10','13'), ('2015-12-11','13'), ('2015-12-12','13'), ('2015-12-13','13'), ('2015-12-14','13'), ('2015-12-15','13'), ('2015-12-16','13'), ('2015-12-17','13'), ('2015-12-18','13'), ('2015-12-19','13'), ('2015-12-20','13'), ('2015-12-21','13'), ('2015-12-22','13'), ('2015-12-23','13'), ('2015-12-24','13'), ('2015-12-25','13'), ('2015-12-26','13'), ('2015-12-27','13'), ('2015-12-28','13'), ('2015-12-29','13'), ('2015-12-30','13'), ('2015-12-31','13'), ('2016-01-01','13'), ('2016-01-02','13'), ('2016-01-03','13'), ('2016-01-04','13'), ('2016-01-05','13'), ('2016-01-06','13'), ('2016-01-07','13'), ('2016-01-08','13'), ('2016-01-09','13'), ('2016-01-10','13'), ('2016-01-11','13'), ('2016-01-12','13'), ('2016-01-13','13'), ('2016-01-14','13'), ('2016-01-15','13'), ('2016-01-16','13'), ('2016-01-17','13'), ('2016-01-18','13'), ('2016-01-19','13')
  10. 0.0003      INSERT INTO `generic` (`date`, `partner_id`) VALUES ('2016-01-20','13'), ('2016-01-21','13'), ('2016-01-22','13'), ('2016-01-23','13'), ('2016-01-24','13'), ('2016-01-25','13'), ('2016-01-26','13'), ('2016-01-27','13'), ('2016-01-28','13'), ('2016-01-29','13'), ('2016-01-30','13'), ('2016-01-31','13'), ('2016-02-01','13'), ('2016-02-02','13'), ('2016-02-03','13'), ('2016-02-04','13'), ('2016-02-05','13'), ('2016-02-06','13'), ('2016-02-07','13'), ('2016-02-08','13'), ('2016-02-09','13'), ('2016-02-10','13'), ('2016-02-11','13'), ('2016-02-12','13'), ('2016-02-13','13'), ('2016-02-14','13'), ('2016-02-15','13'), ('2016-02-16','13'), ('2016-02-17','13'), ('2016-02-18','13'), ('2016-02-19','13'), ('2016-02-20','13'), ('2016-02-21','13'), ('2016-02-22','13'), ('2016-02-23','13'), ('2016-02-24','13'), ('2016-02-25','13'), ('2016-02-26','13'), ('2016-02-27','13'), ('2016-02-28','13'), ('2016-02-29','13'), ('2016-03-01','13'), ('2016-03-02','13'), ('2016-03-03','13'), ('2016-03-04','13'), ('2016-03-05','13'), ('2016-03-06','13'), ('2016-03-07','13'), ('2016-03-08','13'), ('2016-03-09','13'), ('2016-03-10','13'), ('2016-03-11','13'), ('2016-03-12','13'), ('2016-03-13','13'), ('2016-03-14','13'), ('2016-03-15','13'), ('2016-03-16','13'), ('2016-03-17','13'), ('2016-03-18','13'), ('2016-03-19','13'), ('2016-03-20','13'), ('2016-03-21','13'), ('2016-03-22','13'), ('2016-03-23','13'), ('2016-03-24','13'), ('2016-03-25','13'), ('2016-03-26','13'), ('2016-03-27','13'), ('2016-03-28','13'), ('2016-03-29','13'), ('2016-03-30','13'), ('2016-03-31','13'), ('2016-04-01','13'), ('2016-04-02','13'), ('2016-04-03','13'), ('2016-04-04','13'), ('2016-04-05','13'), ('2016-04-06','13'), ('2016-04-07','13'), ('2016-04-08','13'), ('2016-04-09','13'), ('2016-04-10','13'), ('2016-04-11','13'), ('2016-04-12','13'), ('2016-04-13','13'), ('2016-04-14','13'), ('2016-04-15','13'), ('2016-04-16','13'), ('2016-04-17','13'), ('2016-04-18','13'), ('2016-04-19','13'), ('2016-04-20','13'), ('2016-04-21','13'), ('2016-04-22','13'), ('2016-04-23','13'), ('2016-04-24','13'), ('2016-04-25','13'), ('2016-04-26','13'), ('2016-04-27','13'), ('2016-04-28','13')
  11. 0.0003      INSERT INTO `generic` (`date`, `partner_id`) VALUES ('2016-04-29','13'), ('2016-04-30','13'), ('2016-05-01','13'), ('2016-05-02','13'), ('2016-05-03','13'), ('2016-05-04','13'), ('2016-05-05','13'), ('2016-05-06','13'), ('2016-05-07','13'), ('2016-05-08','13'), ('2016-05-09','13'), ('2016-05-10','13'), ('2016-05-11','13'), ('2016-05-12','13'), ('2016-05-13','13'), ('2016-05-14','13'), ('2016-05-15','13'), ('2016-05-16','13'), ('2016-05-17','13'), ('2016-05-18','13'), ('2016-05-19','13'), ('2016-05-20','13'), ('2016-05-21','13'), ('2016-05-22','13'), ('2016-05-23','13'), ('2016-05-24','13'), ('2016-05-25','13'), ('2016-05-26','13'), ('2016-05-27','13'), ('2016-05-28','13'), ('2016-05-29','13'), ('2016-05-30','13'), ('2016-05-31','13'), ('2016-06-01','13'), ('2016-06-02','13'), ('2016-06-03','13'), ('2016-06-04','13'), ('2016-06-05','13'), ('2016-06-06','13'), ('2016-06-07','13'), ('2016-06-08','13'), ('2016-06-09','13'), ('2016-06-10','13'), ('2016-06-11','13'), ('2016-06-12','13'), ('2016-06-13','13'), ('2016-06-14','13'), ('2016-06-15','13'), ('2016-06-16','13'), ('2016-06-17','13'), ('2016-06-18','13'), ('2016-06-19','13'), ('2016-06-20','13'), ('2016-06-21','13'), ('2016-06-22','13'), ('2016-06-23','13'), ('2016-06-24','13'), ('2016-06-25','13'), ('2016-06-26','13'), ('2016-06-27','13'), ('2016-06-28','13'), ('2016-06-29','13'), ('2016-06-30','13'), ('2016-07-01','13'), ('2016-07-02','13'), ('2016-07-03','13'), ('2016-07-04','13'), ('2016-07-05','13'), ('2016-07-06','13'), ('2016-07-07','13'), ('2016-07-08','13'), ('2016-07-09','13'), ('2016-07-10','13')
  12. 0.0036      SELECT `g`.`date`, IFNULL(`u`.`unique_count`, 0) `unique_count`, IFNULL(`r`.`reg_count`, 0) `reg_count`
  13. FROM `generic` `g`
  14. LEFT JOIN (SELECT `date`, `referer_pid`, COUNT(`user_ip`) `unique_count`
  15. FROM `ref_link_unique`
  16. GROUP BY `date`, `referer_pid`) `u` ON `u`.`date` = `g`.`date` and `u`.`referer_pid` = `g`.`partner_id`
  17. LEFT JOIN (SELECT DATE(`registration_time`) `reg_date`, `referer_pid`, COUNT(`id`) `reg_count`
  18. FROM `partners`
  19. WHERE `referer_pid` IS NOT NULL
  20. GROUP BY `reg_date`, `referer_pid`) `r` ON `r`.`reg_date` = `g`.`date` and `u`.`referer_pid` = `g`.`partner_id`
  21. ORDER BY `g`.`date` DESC
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!