Напредни извештаи од базата (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 
    BookTitle,
    CustomerName,
    SUM(SaleAmount) AS TotalProfit
FROM 
    PreviousYearSales
GROUP BY 
    BookTitle, CustomerName
ORDER BY 
    TotalProfit DESC;

Извештај за проверка на профит од продажба за последните две години со преглед на трендови:

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;

Извештај за најпродавана книга по година со детали за жанрови и издавачи:

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;

Извештај за најголеми профити за секоја година со детали за автори со најмногу продадени книги:

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 
    YearlyMaxProfits YMP
JOIN 
    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 
    YMP.OrderYear, A.AuthorName
ORDER BY 
    YMP.OrderYear, TotalProfit DESC;