-
Notifications
You must be signed in to change notification settings - Fork 1
/
statsQuery.sql
128 lines (105 loc) · 5.28 KB
/
statsQuery.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
-- total number of active sellers
SELECT COUNT(farmID)
FROM users
WHERE farmID IS NOT NULL AND status = 'active';
-- no of new sellers per month
-- no need for programming loop
SELECT m as Month, Count(users.userID) as RegUserCount
FROM (
SELECT m, y
FROM
(SELECT YEAR(curdate()) y ) years,
(SELECT 1 m UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
) months ) ym
LEFT JOIN users
ON ym.m = MONTH(users.sellStart)
AND ym.y = YEAR(users.sellStart)
WHERE
(y=YEAR(CURDATE()) AND m<=MONTH(CURDATE()))
OR
(y=YEAR(CURDATE()) AND m>MONTH(CURDATE()))
GROUP BY m;
-- no of coffee types advertised in the current month
SELECT COUNT(*) as Total,
SUM(case WHEN type = 'Liberica' AND MONTH(updateDate) = MONTH(CURRENT_DATE()) then 1 else 0 end) as LibericaCount,
SUM(case WHEN type = 'Robusta' AND MONTH(updateDate) = MONTH(CURRENT_DATE()) then 1 else 0 end) as RobustaCount,
SUM(case WHEN type = 'Arabica' AND MONTH(updateDate) = MONTH(CURRENT_DATE()) then 1 else 0 end) as ArabicaCount
FROM products;
-- no of coffee variations advertised in the current month
SELECT COUNT(*) as Total,
SUM(case WHEN variety = 'roasted' AND MONTH(updateDate) = MONTH(CURRENT_DATE()) then 1 else 0 end) as RoastedCount,
SUM(case WHEN variety = 'dried' AND MONTH(updateDate) = MONTH(CURRENT_DATE()) then 1 else 0 end) as DriedCount,
SUM(case WHEN variety = 'fresh' AND MONTH(updateDate) = MONTH(CURRENT_DATE()) then 1 else 0 end) as FreshCount
FROM products;
-- no of coffee types advertised per month
-- just program this and change 1 to any value from 1-12 to change months
SELECT
IFNULL(MONTH(updateDate), 1) as Month,
IFNULL(COUNT(*), 0) as Total,
IFNULL(SUM(case WHEN type = 'Liberica' AND YEAR(updateDate) = YEAR(CURRENT_DATE()) then 1 else 0 end), 0) as LibericaCount,
IFNULL(SUM(case WHEN type = 'Robusta' AND YEAR(updateDate) = YEAR(CURRENT_DATE()) then 1 else 0 end), 0) as RobustaCount,
IFNULL(SUM(case WHEN type = 'Arabica' AND YEAR(updateDate) = YEAR(CURRENT_DATE()) then 1 else 0 end), 0) as ArabicaCount
FROM products
WHERE MONTH(updateDate) = 1;
-- no of coffee varieties advertised per month
-- just program this and change 1 to any value from 1-12 to change months
SELECT
IFNULL(MONTH(updateDate), 1) as Month,
IFNULL(COUNT(*), 0) as Total,
IFNULL(SUM(case WHEN variety = 'roasted' AND YEAR(updateDate) = YEAR(CURRENT_DATE()) then 1 else 0 end), 0) as RoastedCount,
IFNULL(SUM(case WHEN variety = 'dried' AND YEAR(updateDate) = YEAR(CURRENT_DATE()) then 1 else 0 end), 0) as DriedCount,
IFNULL(SUM(case WHEN variety = 'fresh' AND YEAR(updateDate) = YEAR(CURRENT_DATE()) then 1 else 0 end), 0) as FreshCount
FROM products
WHERE MONTH(updateDate) = 1;
-- no of coffee product trends in the current year
-- just program this and change 1 to any value from 1-12 to change months
SELECT
IFNULL(MONTH(updateDate), 8) as Month,
IFNULL(COUNT(*), 0) as Total,
IFNULL(SUM(case WHEN variety = 'roasted' AND type = 'Robusta' AND YEAR(updateDate) = YEAR(CURRENT_DATE()) then 1 else 0 end), 0) as RRCount,
IFNULL(SUM(case WHEN variety = 'dried' AND type = 'Robusta' AND YEAR(updateDate) = YEAR(CURRENT_DATE()) then 1 else 0 end), 0) as DRCount,
IFNULL(SUM(case WHEN variety = 'fresh' AND type = 'Robusta' AND YEAR(updateDate) = YEAR(CURRENT_DATE()) then 1 else 0 end), 0) as FRCount,
IFNULL(SUM(case WHEN variety = 'roasted' AND type = 'Arabica' AND YEAR(updateDate) = YEAR(CURRENT_DATE()) then 1 else 0 end), 0) as RACount,
IFNULL(SUM(case WHEN variety = 'dried' AND type = 'Arabica' AND YEAR(updateDate) = YEAR(CURRENT_DATE()) then 1 else 0 end), 0) as DACount,
IFNULL(SUM(case WHEN variety = 'fresh' AND type = 'Arabica' AND YEAR(updateDate) = YEAR(CURRENT_DATE()) then 1 else 0 end), 0) as FACount,
IFNULL(SUM(case WHEN variety = 'roasted' AND type = 'Liberica' AND YEAR(updateDate) = YEAR(CURRENT_DATE()) then 1 else 0 end), 0) as RLCount,
IFNULL(SUM(case WHEN variety = 'dried' AND type = 'Liberica' AND YEAR(updateDate) = YEAR(CURRENT_DATE()) then 1 else 0 end), 0) as DLCount,
IFNULL(SUM(case WHEN variety = 'fresh' AND type = 'Liberica' AND YEAR(updateDate) = YEAR(CURRENT_DATE()) then 1 else 0 end), 0) as FLCount
FROM products
WHERE MONTH(updateDate) = 8;
-- no of registered users
SELECT COUNT(*)
FROM users;
-- no of active users
SELECT COUNT(*)
FROM users
WHERE status = 'active';
-- no of banned users
SELECT COUNT(*)
FROM users
WHERE status = 'suspended';
-- no of inactive users
SELECT COUNT(*)
FROM users
WHERE status = 'inactive';
-- no of registered users per month
-- no need for programming loop
SELECT m as Month, Count(users.userID) as RegUserCount
FROM (
SELECT m, y
FROM
(SELECT YEAR(curdate()) y ) years,
(SELECT 1 m UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
) months ) ym
LEFT JOIN users
ON ym.m = MONTH(users.regDate)
AND ym.y = YEAR(users.regDate)
WHERE
(y=YEAR(CURDATE()) AND m<=MONTH(CURDATE()))
OR
(y=YEAR(CURDATE()) AND m>MONTH(CURDATE()))
GROUP BY m;