Changes between Version 10 and Version 11 of AdvancedDatabaseDevelopment

Timestamp:

08/28/26 05:45:59 (10 days ago)

Author:

232012

Comment:

--

AdvancedDatabaseDevelopment

@@ -104 +104 @@
 }}}
 * **Implementation:**  
+
+{{{#!div style="text-align: justify; width: 100%;"
+  First, we create a table where all of the "HOT" products will be inserted. 
+}}}
+
+{{{
+CREATE TABLE IF NOT EXISTS project.hot_item_admin_notes (
+    product_id BIGINT NOT NULL REFERENCES project.products(product_id) ON DELETE CASCADE,
+    release_title VARCHAR NOT NULL,
+    format VARCHAR NOT NULL,
+    total_quantity_sold INTEGER NOT NULL,
+    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
+);
+}}}
+
+{{{#!div style="text-align: justify; width: 100%;"
+  Then, we create a procedure that will place the items into the table mentioned and shown above.
+}}}
+
+{{{
+CREATE OR REPLACE PROCEDURE project.flag_hot_items()
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    INSERT INTO project.hot_item_admin_notes
+    (
+        product_id,
+        release_title,
+        format,
+        total_quantity_sold
+    )
+    SELECT
+        p.product_id,
+        r.title,
+        p.format,
+        SUM(op.quantity)
+    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.status <> 'CANCELLED'
+    GROUP BY
+        p.product_id,
+        r.title,
+        p.format
+    HAVING SUM(op.quantity) > 50
+       AND NOT EXISTS (
+            SELECT 1
+            FROM project.hot_item_admin_notes hin
+            WHERE hin.product_id = p.product_id
+       );
+END;
+$$;
+}}}
 
 == Triggers