NaprednaTema: Analitiki.sql

BEGIN;

DROP SCHEMA IF EXISTS car_dealership_analytics CASCADE;
CREATE SCHEMA car_dealership_analytics;

SET search_path TO car_dealership_analytics, car_dealership;

CREATE TABLE DimDate
(
    date_key    INT PRIMARY KEY,
    full_date   DATE        NOT NULL UNIQUE,
    year        INT         NOT NULL,
    quarter     INT         NOT NULL CHECK (quarter BETWEEN 1 AND 4),
    month       INT         NOT NULL CHECK (month BETWEEN 1 AND 12),
    month_name  VARCHAR(20) NOT NULL,
    day         INT         NOT NULL CHECK (day BETWEEN 1 AND 31),
    day_of_week INT         NOT NULL CHECK (day_of_week BETWEEN 1 AND 7),
    day_name    VARCHAR(20) NOT NULL,
    is_weekend  BOOLEAN     NOT NULL
);

INSERT INTO DimDate
SELECT TO_CHAR(d, 'YYYYMMDD')::INT           AS date_key,
       d::DATE                               AS full_date,
       EXTRACT(YEAR FROM d)::INT             AS year,
       EXTRACT(QUARTER FROM d)::INT          AS quarter,
       EXTRACT(MONTH FROM d)::INT            AS month,
       TO_CHAR(d, 'FMMonth')                 AS month_name,
       EXTRACT(DAY FROM d)::INT              AS day,
       EXTRACT(ISODOW FROM d)::INT           AS day_of_week,
       TO_CHAR(d, 'FMDay')                   AS day_name,
       EXTRACT(ISODOW FROM d)::INT IN (6, 7) AS is_weekend
FROM (SELECT generate_series(
                     COALESCE(MIN(date), CURRENT_DATE),
                     COALESCE(MAX(date), CURRENT_DATE),
                     INTERVAL '1 day'
             )::DATE AS d
      FROM car_dealership.Sale) dates;

CREATE TABLE DimEmployee
(
    employee_key  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    employee_id   INT          NOT NULL UNIQUE,
    employee_name VARCHAR(255) NOT NULL,
    position      VARCHAR(255) NOT NULL
);

INSERT INTO DimEmployee(employee_id, employee_name, position)
SELECT e.id,
       e.first_name || ' ' || e.last_name AS employee_name,
       e.position
FROM car_dealership.Employee e;

CREATE TABLE DimCustomer
(
    customer_key  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id   INT          NOT NULL UNIQUE,
    customer_name VARCHAR(255) NOT NULL,
    email         VARCHAR(255),
    phone         VARCHAR(20),
    city          VARCHAR(255),
    country       VARCHAR(255)
);

INSERT INTO DimCustomer(customer_id, customer_name, email, phone, city, country)
SELECT DISTINCT ON (c.id) c.id,
                          c.first_name || ' ' || c.last_name AS customer_name,
                          c.email,
                          c.phone,
                          a.city,
                          a.country
FROM car_dealership.Customer c
         LEFT JOIN car_dealership.Address a
                   ON a.customer_id = c.id
ORDER BY c.id, a.id;

CREATE TABLE DimVehicle
(
    vehicle_key     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    vin             VARCHAR(17)  NOT NULL UNIQUE,
    brand           VARCHAR(255) NOT NULL,
    model           VARCHAR(255) NOT NULL,
    model_year      INT          NOT NULL,
    vehicle_type    VARCHAR(255) NOT NULL,
    color           VARCHAR(255),
    production_year INT,
    base_price      NUMERIC(12, 2)
);

INSERT INTO DimVehicle(vin, brand, model, model_year, vehicle_type, color, production_year, base_price)
SELECT v.vin,
       b.brand,
       m.model,
       m.year  AS model_year,
       vt.type AS vehicle_type,
       v.color,
       v.production_year,
       v.price AS base_price
FROM car_dealership.Vehicle v
         JOIN car_dealership.Model m
              ON m.id = v.model_id
         JOIN car_dealership.Brand b
              ON b.id = m.brand_id
         JOIN car_dealership.VehicleType vt
              ON vt.id = v.vehicle_type_id;

CREATE TABLE DimPaymentType
(
    payment_type_key BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payment_type     VARCHAR(255) NOT NULL UNIQUE
);

INSERT INTO DimPaymentType(payment_type)
SELECT DISTINCT type
FROM car_dealership.Payment;

CREATE TABLE DimContractType
(
    contract_type_key BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    contract_type     VARCHAR(255) NOT NULL UNIQUE
);

INSERT INTO DimContractType(contract_type)
SELECT DISTINCT type
FROM car_dealership.Contract;

CREATE TABLE FactSales
(
    sale_key            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sale_id             INT            NOT NULL UNIQUE,
    payment_id          INT            NOT NULL UNIQUE,

    date_key            INT            NOT NULL,
    employee_key        BIGINT         NOT NULL,
    customer_key        BIGINT         NOT NULL,
    vehicle_key         BIGINT         NOT NULL,
    payment_type_key    BIGINT         NOT NULL,
    contract_type_key   BIGINT         NOT NULL,

    gross_revenue       NUMERIC(12, 2) NOT NULL,
    discount_percentage NUMERIC(5, 2)  NOT NULL DEFAULT 0,
    discount_amount     NUMERIC(12, 2) NOT NULL DEFAULT 0,
    net_revenue         NUMERIC(12, 2) NOT NULL,
    sale_count          SMALLINT       NOT NULL DEFAULT 1 CHECK (sale_count = 1),

    CONSTRAINT fk_fact_date
        FOREIGN KEY (date_key) REFERENCES DimDate (date_key),
    CONSTRAINT fk_fact_employee
        FOREIGN KEY (employee_key) REFERENCES DimEmployee (employee_key),
    CONSTRAINT fk_fact_customer
        FOREIGN KEY (customer_key) REFERENCES DimCustomer (customer_key),
    CONSTRAINT fk_fact_vehicle
        FOREIGN KEY (vehicle_key) REFERENCES DimVehicle (vehicle_key),
    CONSTRAINT fk_fact_payment_type
        FOREIGN KEY (payment_type_key) REFERENCES DimPaymentType (payment_type_key),
    CONSTRAINT fk_fact_contract_type
        FOREIGN KEY (contract_type_key) REFERENCES DimContractType (contract_type_key)
);

INSERT INTO FactSales(sale_id,
                      payment_id,
                      date_key,
                      employee_key,
                      customer_key,
                      vehicle_key,
                      payment_type_key,
                      contract_type_key,
                      gross_revenue,
                      discount_percentage,
                      discount_amount,
                      net_revenue,
                      sale_count)
SELECT s.id                                                         AS sale_id,
       p.id                                                         AS payment_id,
       dd.date_key,
       de.employee_key,
       dc.customer_key,
       dv.vehicle_key,
       dpt.payment_type_key,
       dct.contract_type_key,
       p.amount                                                     AS gross_revenue,
       COALESCE(d.percentage, 0)::NUMERIC(5, 2)                     AS discount_percentage,
       ROUND(p.amount * COALESCE(d.percentage, 0) / 100.0, 2)       AS discount_amount,
       ROUND(p.amount * (1 - COALESCE(d.percentage, 0) / 100.0), 2) AS net_revenue,
       1                                                            AS sale_count
FROM car_dealership.Sale s
         JOIN car_dealership.Payment p
              ON p.sale_id = s.id
         JOIN car_dealership.Contract con
              ON con.id = s.contract_id
         JOIN DimDate dd
              ON dd.full_date = s.date
         JOIN DimEmployee de
              ON de.employee_id = s.employee_id
         JOIN DimCustomer dc
              ON dc.customer_id = s.customer_id
         JOIN DimVehicle dv
              ON dv.vin = con.vin
         JOIN DimPaymentType dpt
              ON dpt.payment_type = p.type
         JOIN DimContractType dct
              ON dct.contract_type = con.type
         LEFT JOIN car_dealership.Discount d
                   ON d.payment_id = p.id;

CREATE INDEX idx_fact_sales_employee
    ON FactSales (employee_key);

CREATE INDEX idx_fact_sales_customer
    ON FactSales (customer_key);

CREATE INDEX idx_fact_sales_vehicle
    ON FactSales (vehicle_key);

CREATE INDEX idx_fact_sales_payment_type
    ON FactSales (payment_type_key);

CREATE INDEX idx_fact_sales_contract_type
    ON FactSales (contract_type_key);

CREATE INDEX idx_fact_sales_date_vehicle
    ON FactSales (date_key, vehicle_key)
    INCLUDE (gross_revenue, discount_amount, net_revenue, sale_count);

CREATE INDEX idx_fact_sales_date_contract_type
    ON FactSales (date_key, contract_type_key)
    INCLUDE (gross_revenue, discount_amount, net_revenue, sale_count);

CREATE INDEX idx_dim_date_year_month
    ON DimDate (year, month);

CREATE INDEX idx_dim_vehicle_brand_model
    ON DimVehicle (brand, model);

CREATE INDEX idx_dim_customer_city
    ON DimCustomer (city);

CREATE OR REPLACE VIEW RevenueByBrandAndYear AS
SELECT dv.brand,
       dd.year,
       SUM(fs.gross_revenue)         AS total_gross_revenue,
       SUM(fs.discount_amount)       AS total_discount_amount,
       SUM(fs.net_revenue)           AS total_net_revenue,
       SUM(fs.sale_count)            AS total_sales,
       ROUND(AVG(fs.net_revenue), 2) AS avg_net_sale_value
FROM FactSales fs
         JOIN DimVehicle dv
              ON dv.vehicle_key = fs.vehicle_key
         JOIN DimDate dd
              ON dd.date_key = fs.date_key
GROUP BY dv.brand, dd.year;

CREATE OR REPLACE VIEW RevenueByEmployeeAndMonth AS
SELECT de.employee_id,
       de.employee_name,
       de.position,
       dd.year,
       dd.month,
       MAKE_DATE(dd.year, dd.month, 1) AS month_start,
       SUM(fs.gross_revenue)           AS total_gross_revenue,
       SUM(fs.discount_amount)         AS total_discount_amount,
       SUM(fs.net_revenue)             AS total_net_revenue,
       SUM(fs.sale_count)              AS total_sales,
       ROUND(AVG(fs.net_revenue), 2)   AS avg_net_sale_value
FROM FactSales fs
         JOIN DimEmployee de
              ON de.employee_key = fs.employee_key
         JOIN DimDate dd
              ON dd.date_key = fs.date_key
GROUP BY de.employee_id, de.employee_name, de.position, dd.year, dd.month;

CREATE OR REPLACE VIEW SalesByPaymentType AS
SELECT dpt.payment_type,
       SUM(fs.gross_revenue)                                                  AS total_gross_revenue,
       SUM(fs.discount_amount)                                                AS total_discount_amount,
       SUM(fs.net_revenue)                                                    AS total_net_revenue,
       SUM(fs.sale_count)                                                     AS total_sales,
       ROUND(AVG(fs.net_revenue), 2)                                          AS avg_net_sale_value,
       ROUND(100.0 * SUM(fs.sale_count) / SUM(SUM(fs.sale_count)) OVER (), 2) AS sales_percentage
FROM FactSales fs
         JOIN DimPaymentType dpt
              ON dpt.payment_type_key = fs.payment_type_key
GROUP BY dpt.payment_type;

CREATE OR REPLACE VIEW RevenueByCustomerCity AS
SELECT dc.country,
       dc.city,
       SUM(fs.gross_revenue)         AS total_gross_revenue,
       SUM(fs.discount_amount)       AS total_discount_amount,
       SUM(fs.net_revenue)           AS total_net_revenue,
       SUM(fs.sale_count)            AS total_sales,
       ROUND(AVG(fs.net_revenue), 2) AS avg_net_sale_value
FROM FactSales fs
         JOIN DimCustomer dc
              ON dc.customer_key = fs.customer_key
GROUP BY dc.country, dc.city;

CREATE OR REPLACE VIEW RevenueByVehicleType AS
SELECT dv.vehicle_type,
       SUM(fs.gross_revenue)         AS total_gross_revenue,
       SUM(fs.discount_amount)       AS total_discount_amount,
       SUM(fs.net_revenue)           AS total_net_revenue,
       SUM(fs.sale_count)            AS total_sales,
       ROUND(AVG(fs.net_revenue), 2) AS avg_net_sale_value
FROM FactSales fs
         JOIN DimVehicle dv
              ON dv.vehicle_key = fs.vehicle_key
GROUP BY dv.vehicle_type;

CREATE OR REPLACE VIEW RevenueByContractType AS
SELECT dct.contract_type,
       SUM(fs.gross_revenue)         AS total_gross_revenue,
       SUM(fs.discount_amount)       AS total_discount_amount,
       SUM(fs.net_revenue)           AS total_net_revenue,
       SUM(fs.sale_count)            AS total_sales,
       ROUND(AVG(fs.net_revenue), 2) AS avg_net_sale_value
FROM FactSales fs
         JOIN DimContractType dct
              ON dct.contract_type_key = fs.contract_type_key
GROUP BY dct.contract_type;

CREATE MATERIALIZED VIEW SalesCubeBrandYearPaymentContract AS
SELECT CASE WHEN GROUPING(dv.brand) = 1 THEN 'All Brands' ELSE dv.brand END                           AS brand,
       CASE WHEN GROUPING(dd.year) = 1 THEN 'All Years' ELSE dd.year::TEXT END                        AS year_label,
       CASE WHEN GROUPING(dpt.payment_type) = 1 THEN 'All Payment Types' ELSE dpt.payment_type END    AS payment_type,
       CASE WHEN GROUPING(dct.contract_type) = 1 THEN 'All Contract Types' ELSE dct.contract_type END AS contract_type,
       GROUPING(dv.brand)                                                                             AS is_brand_total,
       GROUPING(dd.year)                                                                              AS is_year_total,
       GROUPING(dpt.payment_type)                                                                     AS is_payment_type_total,
       GROUPING(dct.contract_type)                                                                    AS is_contract_type_total,
       SUM(fs.gross_revenue)                                                                          AS total_gross_revenue,
       SUM(fs.discount_amount)                                                                        AS total_discount_amount,
       SUM(fs.net_revenue)                                                                            AS total_net_revenue,
       SUM(fs.sale_count)                                                                             AS total_sales,
       ROUND(AVG(fs.net_revenue), 2)                                                                  AS avg_net_sale_value
FROM FactSales fs
         JOIN DimVehicle dv
              ON dv.vehicle_key = fs.vehicle_key
         JOIN DimDate dd
              ON dd.date_key = fs.date_key
         JOIN DimPaymentType dpt
              ON dpt.payment_type_key = fs.payment_type_key
         JOIN DimContractType dct
              ON dct.contract_type_key = fs.contract_type_key
GROUP BY CUBE (dv.brand, dd.year, dpt.payment_type, dct.contract_type);

CREATE INDEX idx_mv_sales_cube_brand_year_payment_contract
    ON SalesCubeBrandYearPaymentContract (
                                          brand,
                                          year_label,
                                          payment_type,
                                          contract_type,
                                          is_brand_total,
                                          is_year_total,
                                          is_payment_type_total,
                                          is_contract_type_total
        );

CREATE OR REPLACE VIEW SalesCubeBrandYearPayment AS
SELECT brand,
       year_label,
       payment_type,
       is_brand_total,
       is_year_total,
       is_payment_type_total,
       total_gross_revenue,
       total_discount_amount,
       total_net_revenue,
       total_sales,
       avg_net_sale_value
FROM SalesCubeBrandYearPaymentContract
WHERE is_contract_type_total = 1;

CREATE MATERIALIZED VIEW EmployeeSalesRollup AS
SELECT CASE WHEN GROUPING(de.position) = 1 THEN 'All Positions' ELSE de.position END AS position,
       CASE
           WHEN GROUPING(de.employee_id) = 1 THEN NULL
           ELSE de.employee_id
           END                                                                       AS employee_id,
       CASE
           WHEN GROUPING(de.employee_name) = 1 THEN 'All Employees'
           ELSE de.employee_name
           END                                                                       AS employee_name,
       CASE WHEN GROUPING(dd.year) = 1 THEN 'All Years' ELSE dd.year::TEXT END       AS year_label,
       GROUPING(de.position)                                                         AS is_position_total,
       GROUPING(de.employee_id)                                                      AS is_employee_total,
       GROUPING(dd.year)                                                             AS is_year_total,
       SUM(fs.gross_revenue)                                                         AS total_gross_revenue,
       SUM(fs.discount_amount)                                                       AS total_discount_amount,
       SUM(fs.net_revenue)                                                           AS total_net_revenue,
       SUM(fs.sale_count)                                                            AS total_sales,
       ROUND(AVG(fs.net_revenue), 2)                                                 AS avg_net_sale_value
FROM FactSales fs
         JOIN DimEmployee de
              ON de.employee_key = fs.employee_key
         JOIN DimDate dd
              ON dd.date_key = fs.date_key
GROUP BY GROUPING SETS
    ( (de.position, de.employee_id, de.employee_name, dd.year),
      (de.position, dd.year),
      (dd.year),
      (de.position, de.employee_id, de.employee_name),
      (de.position),
    ()
    );

CREATE INDEX idx_mv_employee_sales_rollup
    ON EmployeeSalesRollup (
                            position,
                            employee_id,
                            year_label,
                            is_position_total,
                            is_employee_total,
                            is_year_total
        );

CREATE OR REPLACE VIEW EmployeeCubePositionEmployeeYear AS
SELECT position,
       employee_id,
       employee_name,
       year_label,
       is_position_total,
       is_employee_total,
       is_year_total,
       total_gross_revenue,
       total_discount_amount,
       total_net_revenue,
       total_sales,
       avg_net_sale_value
FROM EmployeeSalesRollup;

CREATE MATERIALIZED VIEW VehicleCubeTypeBrandYear AS
SELECT CASE WHEN GROUPING(dv.vehicle_type) = 1 THEN 'All Vehicle Types' ELSE dv.vehicle_type END AS vehicle_type,
       CASE WHEN GROUPING(dv.brand) = 1 THEN 'All Brands' ELSE dv.brand END                      AS brand,
       CASE WHEN GROUPING(dd.year) = 1 THEN 'All Years' ELSE dd.year::TEXT END                   AS year_label,
       GROUPING(dv.vehicle_type)                                                                 AS is_vehicle_type_total,
       GROUPING(dv.brand)                                                                        AS is_brand_total,
       GROUPING(dd.year)                                                                         AS is_year_total,
       SUM(fs.gross_revenue)                                                                     AS total_gross_revenue,
       SUM(fs.discount_amount)                                                                   AS total_discount_amount,
       SUM(fs.net_revenue)                                                                       AS total_net_revenue,
       SUM(fs.sale_count)                                                                        AS total_sales,
       ROUND(AVG(fs.net_revenue), 2)                                                             AS avg_net_sale_value
FROM FactSales fs
         JOIN DimVehicle dv
              ON dv.vehicle_key = fs.vehicle_key
         JOIN DimDate dd
              ON dd.date_key = fs.date_key
GROUP BY CUBE (dv.vehicle_type, dv.brand, dd.year);

CREATE INDEX idx_mv_vehicle_cube_type_brand_year
    ON VehicleCubeTypeBrandYear (
                                 vehicle_type,
                                 brand,
                                 year_label,
                                 is_vehicle_type_total,
                                 is_brand_total,
                                 is_year_total
        );

ANALYZE DimDate;
ANALYZE DimEmployee;
ANALYZE DimCustomer;
ANALYZE DimVehicle;
ANALYZE DimPaymentType;
ANALYZE DimContractType;
ANALYZE FactSales;
ANALYZE SalesCubeBrandYearPaymentContract;
ANALYZE EmployeeSalesRollup;
ANALYZE VehicleCubeTypeBrandYear;

COMMIT;

SELECT *
FROM RevenueByBrandAndYear
ORDER BY total_net_revenue DESC
LIMIT 20;

SELECT *
FROM RevenueByEmployeeAndMonth
ORDER BY total_net_revenue DESC
LIMIT 20;

SELECT *
FROM SalesByPaymentType
ORDER BY total_net_revenue DESC;

SELECT *
FROM RevenueByContractType
ORDER BY total_net_revenue DESC;

SELECT *
FROM RevenueByCustomerCity
ORDER BY total_net_revenue DESC
LIMIT 20;

SELECT *
FROM SalesCubeBrandYearPaymentContract
ORDER BY total_net_revenue DESC NULLS LAST
LIMIT 50;

SELECT *
FROM EmployeeSalesRollup
ORDER BY total_net_revenue DESC NULLS LAST
LIMIT 50;

SELECT *
FROM VehicleCubeTypeBrandYear
ORDER BY total_net_revenue DESC NULLS LAST
LIMIT 50;


-- =========================================================
-- OPTIONAL VALIDATION AND INDEX ANALYSIS
-- Run after the Data Cube build finishes.
-- =========================================================

SET search_path TO car_dealership_analytics, car_dealership;

-- Refresh statistics after the large analytical load.
VACUUM ANALYZE FactSales;
VACUUM ANALYZE DimDate;
VACUUM ANALYZE DimEmployee;
VACUUM ANALYZE DimCustomer;
VACUUM ANALYZE DimVehicle;
VACUUM ANALYZE DimPaymentType;
VACUUM ANALYZE DimContractType;
VACUUM ANALYZE SalesCubeBrandYearPaymentContract;
VACUUM ANALYZE EmployeeSalesRollup;
VACUUM ANALYZE VehicleCubeTypeBrandYear;


-- =========================================================
-- GLOBAL FACT CHECK
-- Confirms one fact row equals one sale, and revenue math is consistent.
-- Expected: fact_rows = total_sales and gross_revenue - discount_amount = net_revenue.
-- =========================================================

SELECT COUNT(*)                                  AS fact_rows,
       SUM(sale_count)                           AS total_sales,
       SUM(gross_revenue)                        AS gross_revenue,
       SUM(discount_amount)                      AS discount_amount,
       SUM(net_revenue)                          AS net_revenue,
       SUM(gross_revenue) - SUM(discount_amount) AS calculated_net_revenue
FROM FactSales;


-- =========================================================
-- INDEX USAGE CHECKS
-- Look for Index Scan, Bitmap Index Scan, or Index Only Scan in the output.
-- =========================================================

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM FactSales
WHERE employee_key = (SELECT employee_key
                      FROM FactSales
                      LIMIT 1);
-- Checks idx_fact_sales_employee.

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM FactSales
WHERE customer_key = (SELECT customer_key
                      FROM FactSales
                      LIMIT 1);
-- Checks idx_fact_sales_customer.

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM FactSales
WHERE vehicle_key = (SELECT vehicle_key
                     FROM FactSales
                     LIMIT 1);
-- Checks idx_fact_sales_vehicle.

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM FactSales
WHERE payment_type_key = (SELECT payment_type_key
                          FROM FactSales
                          LIMIT 1);
-- Checks idx_fact_sales_payment_type.

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM FactSales
WHERE contract_type_key = (SELECT contract_type_key
                           FROM FactSales
                           LIMIT 1);
-- Checks idx_fact_sales_contract_type.

EXPLAIN (ANALYZE, BUFFERS)
SELECT gross_revenue, discount_amount, net_revenue, sale_count
FROM FactSales
WHERE (date_key, vehicle_key) = (SELECT date_key, vehicle_key
                                 FROM FactSales
                                 LIMIT 1);
-- Checks idx_fact_sales_date_vehicle. Index Only Scan shows the INCLUDE columns helped.

EXPLAIN (ANALYZE, BUFFERS)
SELECT gross_revenue, discount_amount, net_revenue, sale_count
FROM FactSales
WHERE (date_key, contract_type_key) = (SELECT date_key, contract_type_key
                                       FROM FactSales
                                       LIMIT 1);
-- Checks idx_fact_sales_date_contract_type. Index Only Scan shows the INCLUDE columns helped.

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM DimDate
WHERE (year, month) = (SELECT year, month
                       FROM DimDate
                       LIMIT 1);
-- Checks idx_dim_date_year_month.

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM DimVehicle
WHERE (brand, model) = (SELECT brand, model
                        FROM DimVehicle
                        LIMIT 1);
-- Checks idx_dim_vehicle_brand_model.

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM DimCustomer
WHERE city = (SELECT city
              FROM DimCustomer
              WHERE city IS NOT NULL
              LIMIT 1);
-- Checks idx_dim_customer_city.

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM SalesCubeBrandYearPaymentContract
WHERE (brand, year_label, payment_type, contract_type) = (SELECT brand, year_label, payment_type, contract_type
                                                          FROM SalesCubeBrandYearPaymentContract
                                                          WHERE is_brand_total = 0
                                                            AND is_year_total = 0
                                                            AND is_payment_type_total = 0
                                                            AND is_contract_type_total = 0
                                                          LIMIT 1);
-- Checks idx_mv_sales_cube_brand_year_payment_contract.

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM EmployeeSalesRollup
WHERE (position, employee_id, year_label) = (SELECT position, employee_id, year_label
                                             FROM EmployeeSalesRollup
                                             WHERE is_position_total = 0
                                               AND is_employee_total = 0
                                               AND is_year_total = 0
                                             LIMIT 1);
-- Checks idx_mv_employee_sales_rollup.

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM VehicleCubeTypeBrandYear
WHERE (vehicle_type, brand, year_label) = (SELECT vehicle_type, brand, year_label
                                           FROM VehicleCubeTypeBrandYear
                                           WHERE is_vehicle_type_total = 0
                                             AND is_brand_total = 0
                                             AND is_year_total = 0
                                           LIMIT 1);
-- Checks idx_mv_vehicle_cube_type_brand_year.


-- =========================================================
-- INDEX BENEFIT CHECK TEMPLATE
-- Use this pattern for any index. ROLLBACK restores the dropped index.
-- Compare Execution Time and Buffers before and after DROP INDEX.
-- =========================================================

BEGIN;

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM FactSales
WHERE employee_key = (SELECT employee_key
                      FROM FactSales
                      LIMIT 1);

DROP INDEX idx_fact_sales_employee;

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM FactSales
WHERE employee_key = (SELECT employee_key
                      FROM FactSales
                      LIMIT 1);

ROLLBACK;


-- =========================================================
-- VIEW VALIDATION CHECKS
-- Manual totals should match the corresponding view/materialized view totals.
-- =========================================================

SELECT *
FROM RevenueByBrandAndYear
ORDER BY total_net_revenue DESC
LIMIT 20;

SELECT dv.brand,
       dd.year,
       SUM(fs.gross_revenue)         AS total_gross_revenue,
       SUM(fs.discount_amount)       AS total_discount_amount,
       SUM(fs.net_revenue)           AS total_net_revenue,
       SUM(fs.sale_count)            AS total_sales,
       ROUND(AVG(fs.net_revenue), 2) AS avg_net_sale_value
FROM FactSales fs
         JOIN DimVehicle dv ON dv.vehicle_key = fs.vehicle_key
         JOIN DimDate dd ON dd.date_key = fs.date_key
GROUP BY dv.brand, dd.year
ORDER BY total_net_revenue DESC
LIMIT 20;

SELECT total_net_revenue, total_sales
FROM SalesCubeBrandYearPaymentContract
WHERE is_brand_total = 1
  AND is_year_total = 1
  AND is_payment_type_total = 1
  AND is_contract_type_total = 1;

SELECT SUM(net_revenue) AS total_net_revenue,
       SUM(sale_count)  AS total_sales
FROM FactSales;