Changes between Version 4 and Version 5 of AdvancedDatabaseDevelopment

Timestamp:

06/05/26 14:03:07 (7 days ago)

Author:

232012

Comment:

--

AdvancedDatabaseDevelopment

@@ -40 +40 @@
 
 {{{#!div style="text-align: justify; width: 100%;"
-Combines the entire metadata of a music release into one detailed overview. For any given release, it aggregates its main artists, features, tracks, total track duration, and provides a real-time summary breakdown of available warehouse stock and pricing for all physical formats simultaneously.
+**Description:** Combines the entire metadata of a music release into one detailed overview. For any given release, it aggregates its main artists, features, tracks, total track duration, and provides a real-time summary breakdown of available warehouse stock and pricing for all physical formats simultaneously.
+}}}
+**Implementation:
+{{{
+DROP VIEW IF EXISTS project.detailed_release_view;
+
+CREATE VIEW project.detailed_release_view AS
+WITH main_artists AS (
+    SELECT
+        ra.release_id,
+        string_agg(a.artist_name, ', ' ORDER BY ra.release_ordinal) AS main_artists
+    FROM project.release_artists ra
+    JOIN project.artists a ON a.artist_id = ra.artist_id
+    WHERE ra.type = 'MAIN'
+    GROUP BY ra.release_id
+),
+album_tracks AS (
+    SELECT
+        al.release_id,
+        jsonb_agg(
+            jsonb_build_object(
+                'song_name', s.song_name,
+                'duration', s.song_duration,
+                'features', COALESCE(sf.features, ' ')
+            )
+            ORDER BY s.song_id
+        ) AS tracks,
+        SUM(
+            split_part(s.song_duration, ':', 1)::INT * INTERVAL '1 minute'
+            + split_part(s.song_duration, ':', 2)::INT * INTERVAL '1 second'
+        ) AS total_duration
+    FROM project.albums al
+    JOIN project.album_songs als ON als.album_id = al.release_id
+    JOIN project.songs s ON s.song_id = als.song_id
+    LEFT JOIN (
+        SELECT
+            sa.song_id,
+            string_agg(a.artist_name, ', ' ORDER BY sa.song_ordinal) AS features
+        FROM project.song_artists sa
+        JOIN project.artists a ON a.artist_id = sa.artist_id
+        WHERE sa.song_ordinal > 1
+        GROUP BY sa.song_id
+    ) sf ON sf.song_id = s.song_id
+    GROUP BY al.release_id
+),
+single_tracks AS (
+    SELECT
+        sr.release_id,
+        jsonb_build_array(
+            jsonb_build_object(
+                'song_name', r.title,
+                'duration', sr.duration,
+                'features', COALESCE(sf.features, 'No features')
+            )
+        ) AS tracks,
+        split_part(sr.duration, ':', 1)::INT * INTERVAL '1 minute'
+        + split_part(sr.duration, ':', 2)::INT * INTERVAL '1 second' AS total_duration
+    FROM project.single_releases sr
+    JOIN project.releases r ON r.release_id = sr.release_id
+    LEFT JOIN (
+        SELECT
+            ra.release_id,
+            string_agg(a.artist_name, ', ' ORDER BY ra.release_ordinal) AS features
+        FROM project.release_artists ra
+        JOIN project.artists a ON a.artist_id = ra.artist_id
+        WHERE ra.type = 'FEATURE'
+        GROUP BY ra.release_id
+    ) sf ON sf.release_id = sr.release_id
+),
+product_summary AS (
+    SELECT
+        p.release_id,
+        jsonb_agg(
+            jsonb_build_object(
+                'format', p.format,
+                'price', p.price,
+                'stock', p.stock,
+                'description', p.product_description
+            )
+            ORDER BY p.format
+        ) AS physical_formats
+    FROM project.products p
+    GROUP BY p.release_id
+)
+SELECT
+    r.title,
+    r.record_label,
+    r.genre,
+    r.release_date,
+    r.cover_photo,
+    CASE
+        WHEN al.release_id IS NOT NULL THEN 'ALBUM'
+        ELSE 'SINGLE'
+    END AS release_type,
+    ma.main_artists,
+    COALESCE(at.tracks, st.tracks) AS tracks,
+    COALESCE(at.total_duration, st.total_duration) AS total_track_duration,
+    ps.physical_formats
+FROM project.releases r
+LEFT JOIN project.albums al ON al.release_id = r.release_id
+LEFT JOIN main_artists ma ON ma.release_id = r.release_id
+LEFT JOIN album_tracks at ON at.release_id = r.release_id
+LEFT JOIN single_tracks st ON st.release_id = r.release_id
+LEFT JOIN product_summary ps ON ps.release_id = r.release_id;
 }}}
 
@@ -46 +149 @@
 
 {{{#!div style="text-align: justify; width: 100%;"
-A detailed analytical view that uses a the previous 7-day period to show the Top 10 best-selling products. It combines data from multiple tables, calculates the total quantity sold, excludes cancelled orders, and sorts the products from highest to lowest selling.
+**Description:** A detailed analytical view that uses a the previous 7-day period to show the Top 10 best-selling products. It combines data from multiple tables, calculates the total quantity sold, excludes cancelled orders, and sorts the products from highest to lowest selling.
 }}}
+**Implementation:**
+{{{
+DROP VIEW IF EXISTS project.top_10_highest_selling_products;
+
+CREATE VIEW project.top_10_highest_selling_products AS
+SELECT
+    p.format,
+    p.product_description,
+    r.title AS release_title,
+    r.genre,
+    r.record_label,
+    r.release_date,
+    SUM(op.quantity) AS total_quantity_sold,
+    SUM(op.quantity * op.price_at_purchase) AS total_revenue
+FROM project.order_products op
+JOIN project.orders o ON o.order_id = op.order_id
+JOIN project.products p ON p.product_id = op.product_id
+JOIN project.releases r ON r.release_id = p.release_id
+WHERE o.purchase_date >= CURRENT_DATE - INTERVAL '7 days'
+  AND o.purchase_date < CURRENT_DATE
+  AND o.status <> 'CANCELLED'
+GROUP BY
+    p.product_id,
+    p.format,
+    p.product_description,
+    r.title,
+    r.genre,
+    r.record_label,
+    r.release_date
+ORDER BY total_quantity_sold DESC, total_revenue DESC
+LIMIT 10;
+}}}