CREATE TABLE `track_conversion` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `impression_id` int(11) NOT NULL,
  `earned` decimal(10,2) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_740A9EFA3BA46B6` (`impression_id`),
  KEY `conversion_created_at_idx` (`created_at`),
  KEY `conversion_created_at_earned_idx` (`created_at`, `earned`),
  CONSTRAINT `track_conversion_ibfk_1` FOREIGN KEY (`impression_id`) REFERENCES `track_impression` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=118011 DEFAULT CHARSET=latin1;


CREATE TABLE `track_impression` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `path_id` int(11) NOT NULL,
  `offer_id` int(11) NOT NULL,
  `creative_id` int(11) NOT NULL,
  `visitor_id` bigint(20) DEFAULT NULL,
  `path_position` int(11) NOT NULL,
  `clicked` tinyint(1) NOT NULL,
  `clicked_at` datetime DEFAULT NULL,
  `hash` varchar(32) NOT NULL,
  `user_hash` varchar(32) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_9E8A3F1AD96C566B` (`path_id`),
  KEY `IDX_9E8A3F1A53C674EE` (`offer_id`),
  KEY `IDX_9E8A3F1A8E0ED468` (`creative_id`),
  KEY `IDX_9E8A3F1A70BEE6D` (`visitor_id`),
  KEY `hash` (`hash`),
  KEY `track_created_at_path_offer_clicked_user_hash_idx` (`created_at`,`path_id`,`offer_id`,`clicked`,`user_hash`),
  CONSTRAINT `track_impression_ibfk_2` FOREIGN KEY (`path_id`) REFERENCES `path` (`id`) ON DELETE CASCADE,
  CONSTRAINT `track_impression_ibfk_3` FOREIGN KEY (`offer_id`) REFERENCES `offer` (`id`) ON DELETE CASCADE,
  CONSTRAINT `track_impression_ibfk_4` FOREIGN KEY (`creative_id`) REFERENCES `creative` (`id`) ON DELETE CASCADE,
  CONSTRAINT `track_impression_ibfk_5` FOREIGN KEY (`visitor_id`) REFERENCES `visitor` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=983044 DEFAULT CHARSET=latin1;


# QUERY

SELECT p.name                      path_name,
       i.path_id,
       o.name                      offer_name,
       i.offer_id,
       i.path_position,
       SUM(i.clicked)         click_count,
       COUNT(DISTINCT i.user_hash) user_count,
       COUNT(i.id)                 impression_count,
       COUNT(c.id)                 conversion_count,
       SUM(c.earned)               revenue
FROM   track_impression i
       LEFT JOIN track_conversion c
         ON c.impression_id = i.id
       LEFT JOIN path p
         ON p.id = i.path_id
       LEFT JOIN offer o
         ON o.id = i.offer_id
WHERE  i.created_at BETWEEN '2011-09-12 07:00:00' AND '2011-09-13 07:00:00'
GROUP  BY i.path_id,
          i.offer_id;


# EXPLAIN

# id	select_type	table	type	possible_keys		key			key_len	ref			rows	Extra
# 1	SIMPLE		i	ALL	track_created_at_p..	NULL			NULL	NULL			983169	Using where; Using filesort
# 1	SIMPLE		c	eq_ref	UNIQ_740A9EFA3BA46B6	UNIQ_740A9EFA3BA46B6	4	dealfuse_pms.i.id	1
# 1	SIMPLE		p	eq_ref	PRIMARY			PRIMARY			4	dealfuse_pms.i.path_id	1
# 1	SIMPLE		o	eq_ref	PRIMARY			PRIMARY			4	dealfuse_pms.i.offer_id	1