Changes between Version 5 and Version 6 of P4

Timestamp:

01/27/24 14:28:09 (5 months ago)

Author:

216091

Comment:

--

P4

@@ -1 +1 @@
 === Извештај за проверка на профит од продажба за претходната година со детали за книги и клиенти:
 {{{#!sql
+WITH PreviousYearSales AS (
+    SELECT 
+        B.Title AS BookTitle,
+        C.FirstName || ' ' || C.LastName AS CustomerName,
+        O.TotalAmount AS SaleAmount,
+        EXTRACT(YEAR FROM O.OrderDate) AS OrderYear
+    FROM 
+        Book B
+    JOIN 
+        OrderTable O ON B.BookID = O.OrderID
+    JOIN 
+        CustomerOrder CO ON O.OrderID = CO.OrderID
+    JOIN 
+        Customer C ON CO.CustomerID = C.CustomerID
+    WHERE 
+        EXTRACT(YEAR FROM O.OrderDate) = EXTRACT(YEAR FROM CURRENT_DATE) - 1
+)
 SELECT 
-    B.Title AS BookTitle,
+    BookTitle,
+    CustomerName,
+    SUM(SaleAmount) AS TotalProfit
+FROM 
+    PreviousYearSales
+GROUP BY 
+    BookTitle, CustomerName
+ORDER BY 
+    TotalProfit DESC;
+
+}}}
+
+=== Извештај за проверка на профит од продажба за последните две години со анализа на трендови:
+{{{#!sql
+WITH TwoYearsSales AS (
+    SELECT 
+        B.Title AS BookTitle,
+        O.TotalAmount AS SaleAmount,
+        EXTRACT(YEAR FROM O.OrderDate) AS OrderYear
+    FROM 
+        Book B
+    JOIN 
+        OrderTable O ON B.BookID = O.OrderID
+    WHERE 
+        EXTRACT(YEAR FROM O.OrderDate) >= EXTRACT(YEAR FROM CURRENT_DATE) - 2
+)
+SELECT 
+    BookTitle,
+    OrderYear,
+    SaleAmount,
+    LAG(SaleAmount) OVER (PARTITION BY BookTitle ORDER BY OrderYear) AS PreviousYearSaleAmount,
+    COALESCE((SaleAmount - LAG(SaleAmount) OVER (PARTITION BY BookTitle ORDER BY OrderYear)), SaleAmount) AS SaleChange
+FROM 
+    TwoYearsSales
+ORDER BY 
+    BookTitle, OrderYear;
+}}}
+=== Извештај за најпродавана книга по година со детали за жанрови и издавачи:
+{{{#!sql
+WITH BookDetails AS (
+    SELECT 
+        B.Title AS BookTitle,
+        G.GenreName,
+        P.PublisherName,
+        EXTRACT(YEAR FROM O.OrderDate) AS OrderYear,
+        SUM(O.TotalAmount) AS TotalProfit
+    FROM 
+        Book B
+    JOIN 
+        OrderTable O ON B.BookID = O.OrderID
+    JOIN 
+        BookGenre BG ON B.BookID = BG.BookID
+    JOIN 
+        Genre G ON BG.GenreID = G.GenreID
+    JOIN 
+        BookPublisher BP ON B.BookID = BP.BookID
+    JOIN 
+        Publisher P ON BP.PublisherID = P.PublisherID
+    GROUP BY 
+        BookTitle, GenreName, PublisherName, OrderYear
+)
+SELECT 
+    BookTitle,
+    GenreName,
+    PublisherName,
+    OrderYear,
+    TotalProfit
+FROM 
+    BookDetails
+ORDER BY 
+    OrderYear, TotalProfit DESC;
+}}}
+=== Извештај за најголеми профити за секоја година со детали за најпродавани автори:
+{{{#!sql
+WITH YearlyMaxProfits AS (
+    SELECT 
+        EXTRACT(YEAR FROM O.OrderDate) AS OrderYear,
+        MAX(SUM(O.TotalAmount)) AS MaxTotalProfit
+    FROM 
+        OrderTable O
+    GROUP BY 
+        OrderYear
+)
+SELECT 
+    YMP.OrderYear,
+    A.AuthorName,
     SUM(O.TotalAmount) AS TotalProfit
 FROM 
-    Book B
+    YearlyMaxProfits YMP
 JOIN 
-    OrderTable O ON B.BookID = O.OrderID
-WHERE 
-    EXTRACT(YEAR FROM O.OrderDate) = EXTRACT(YEAR FROM CURRENT_DATE) - 1
+    OrderTable O ON EXTRACT(YEAR FROM O.OrderDate) = YMP.OrderYear
+JOIN 
+    Book B ON O.OrderID = B.BookID
+JOIN 
+    BookAuthor BA ON B.BookID = BA.BookID
+JOIN 
+    Author A ON BA.AuthorID = A.AuthorID
 GROUP BY 
-    Betitled
+    YMP.OrderYear, A.AuthorName
 ORDER BY 
-    TotalProfit DESC;
+    YMP.OrderYear, TotalProfit DESC;
 }}}