Changes between Version 32 and Version 33 of AdvancedReports

Timestamp:

07/22/24 14:31:59 (2 months ago)

Author:

184006

Comment:

--

AdvancedReports

@@ -102 +102 @@
 == Прикажување на продукт кој е прв додаден во системот за продажба и за него постојат најмалку нарачки кои биле направени од корисниците.
 {{{
-   SELECT
-    p.id AS product_id,
-    p.name AS product_name,
-    p.description AS product_description,
-    p.price AS product_price,
-    MIN(o.quantity) AS sold_quantity,
-    p.dateadded AS date_added
-FROM
-    project.products p
-LEFT JOIN
-    project.product_are_made_orders pamo ON p.id = pamo.id_products
-LEFT JOIN
-    project.orders o ON pamo.id_orders = o.id
-GROUP BY
-    p.id, p.name, p.description, p.price, p.dateadded
-ORDER BY
-    sold_quantity, date_added
-LIMIT 1;
+WITH ProductSales AS (
+    SELECT
+        p.id AS product_id,
+        p.name AS product_name,
+        p.description AS product_description,
+        p.price AS product_price,
+        MIN(o.quantity) AS sold_quantity,
+        p.dateadded AS date_added
+    FROM
+        project.products p
+    LEFT JOIN
+        project.product_are_made_orders pamo ON p.id = pamo.id_products
+    LEFT JOIN
+        project.orders o ON pamo.id_orders = o.id
+    GROUP BY
+        p.id, p.name, p.description, p.price, p.dateadded
+),
+
+RankedProducts AS (
+    SELECT
+        *,
+        ROW_NUMBER() OVER (ORDER BY sold_quantity, date_added) AS rn
+    FROM
+        ProductSales
+)
+
+SELECT
+    product_id,
+    product_name,
+    product_description,
+    product_price,
+    sold_quantity,
+    date_added
+FROM
+    RankedProducts
+WHERE
+    rn = 1;
+
 }}}
 == Секој ветеринарен центар колку вкупно миленичиња примил и колку е просечната возраст во секој ветеринарен центар.