1 files changed, 27 insertions, 0 deletions

diff --git a/db/sql/views.sql b/db/sql/views.sql
new file mode 100644
index 0000000..c56286d
--- /dev/null
+++ b/db/sql/views.sql
@@ -0,0 +1,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;
\ No newline at end of file