· 7 years ago · Feb 21, 2019, 02:34 AM
1CREATE TABLE IF NOT EXISTS `lf_restaurants` (
2 `r_id` int(8) NOT NULL AUTO_INCREMENT,
3 `r_name` varchar(128) DEFAULT NOT NULL,
4 PRIMARY KEY (`r_id`)
5) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
6
7INSERT INTO `lf_restaurants` (`eo_id`, `eo_ref_id`) VALUES
8('1', 'Restaurant X'),
9('2', 'Cafe Y');
10
11CREATE TABLE IF NOT EXISTS `ecom_orders` (
12 `eo_id` mediumint(9) NOT NULL AUTO_INCREMENT,
13 `eo_ref_id` varchar(12) DEFAULT NOT NULL,
14 `eo_email` varchar(255) DEFAULT NOT NULL,
15 `eo_order_parent` int(11) NOT NULL,
16 PRIMARY KEY (`eo_id`),
17) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;
18
19INSERT INTO `ecom_orders` (`eo_id`, `eo_ref_id`, `eo_email`, `eo_order_parent`) VALUES
20('', '', 'a@a.com', '1'),
21('', '', 'a@a.com', '1'),
22('', '', 'a@a.com', '1'),
23('', '', 'a@a.com', '1'),
24('', '', 'a@a.com', '1'),
25('', '', 'b@b.com', '1'),
26('', '', 'b@b.com', '1'),
27('', '', 'c@c.com', '1'),
28('', '', 'd@d.com', '1'),
29('', '', 'e@e.com', '1'),
30('', '', 'a@a.com', '2'),
31('', '', 'c@c.com', '2'),
32('', '', 'c@c.com', '2'),
33('', '', 'e@e.com', '2');
34
35SELECT r_name,
36 (SELECT COUNT(*) AS cnt_users
37 FROM (
38 SELECT *
39 FROM ecom_orders
40 WHERE eo_order_parent = r_id
41 GROUP BY eo_email
42 ) AS cnt_dummy
43 ) AS num_orders,
44 (SELECT COUNT(*) AS cnt
45 FROM ecom_orders
46 WHERE eo_order_parent = r_id
47 GROUP BY eo_order_parent, eo_email
48
49 ) AS num_rep_orders
50 FROM lf_restaurants
51 ORDER BY num_orders DESC
52
53SELECT eo_order_parent, eo_email, COUNT(eo_email) AS orders FROM ecom_orders
54 GROUP BY eo_order_parent, eo_email
55 HAVING orders > 1;
56
57SELECT eo_order_parent, COUNT(eo_email) FROM ecom_orders
58 GROUP BY eo_order_parent;
59
60SELECT eo_order_parent,
61 SUM(CASE WHEN orders > 1 THEN 1 ELSE 0 END) AS repeats,
62 SUM(1) AS total FROM
63 (
64 SELECT eo_order_parent, eo_email, COUNT(*) AS orders FROM ecom_orders
65 GROUP BY eo_order_parent, eo_email
66 ) AS eo_group_1
67GROUP BY eo_order_parent;
68
69+-----------------+---------+-------+
70| eo_order_parent | repeats | total |
71+-----------------+---------+-------+
72| 1 | 2 | 5 |
73| 2 | 1 | 3 |
74+-----------------+---------+-------+
752 rows in set (0.00 sec)
76
77SELECT
78 u.r_id,
79 u.r_name,
80 SUM(u.no_orders > 1) AS repeats,
81 SUM(u.no_orders) AS orders,
82 COUNT(u.eo_email) AS customers
83FROM (
84 SELECT
85 r.*,
86 o.eo_email,
87 COUNT(o.eo_id) AS no_orders
88 FROM lf_restaurants r
89 LEFT JOIN ecom_orders o ON o.eo_order_parent = r.r_id
90 GROUP BY o.eo_email
91) u
92GROUP BY
93 r.r_id;