From 968481312058aa58261d41cf3460b45109fec240 Mon Sep 17 00:00:00 2001 From: Zach Berwaldt Date: Sat, 16 Mar 2024 10:48:52 -0400 Subject: clean up database scripts, start readmes. --- db/sql/views.sql | 27 +++++++++++++++++++++++++++ 1 file changed, 27 insertions(+) create mode 100644 db/sql/views.sql (limited to 'db/sql/views.sql') 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 -- cgit v1.1