db/sql/views.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

CREATE VIEW IF NOT EXISTS aggregated_stats AS
    SELECT u.uuid, SUM(s.quantity * s.size) from Statistics s INNER JOIN Users u ON u.id = s.user_id INNER JOIN Preferences p ON p.user_id = u.id INNER JOIN Size s ON s.id = p.size_id;

CREATE VIEW IF NOT EXISTS `DailyUserStatistics` AS
SELECT users.name, IFNULL(SUM(statistics.quantity), 0) as total, preferences.color as color
FROM users
LEFT JOIN statistics ON users.id = statistics.user_id AND DATE(statistics.date) = DATE('now', '-1 day')
LEFT JOIN preferences ON users.id = preferences.user_id
GROUP BY users.name;


CREATE VIEW IF NOT EXISTS `WeeklyStatisticsView` AS
  WITH DateSequence(Dates) AS
    (
     SELECT Date(CURRENT_DATE, '-7 day')
     UNION ALL
     SELECT Date(Dates, '+1 day')
     FROM DateSequence
     WHERE Date(Dates, '+1 day') < Date(CURRENT_DATE)
    )
SELECT DateSequence.Dates as 'date',
  IFNULL(SUM(statistics.quantity), 0) AS 'total'
FROM DateSequence
LEFT JOIN statistics
ON Date(statistics.date) = DateSequence.Dates
GROUP BY DateSequence.Dates
ORDER BY DateSequence.Dates;