Погледи

Овој поглед е за сите активни договори кои постојат.

CREATE OR REPLACE VIEW active_agreements_view AS
SELECT
    a.A_Id, 
    a.Price AS agreement_price, 
    a.Status AS agreement_status, 
    a.Datum AS agreement_date, 
    a.Tax_Nr AS dealership_tax_nr, 
    a.VIN AS car_vin,
    a.EMBG AS client_embg,
    
    d.D_Name AS dealership_name, 
    d.Email AS dealership_email, 

    (SELECT string_agg(phone_number, ', ') 
     FROM dealership_telephones dt 
     WHERE dt.dealership_tax_nr = d.Tax_Nr) AS dealership_telephones,

    (SELECT string_agg(brand_name, ', ') 
     FROM dealership_brands db 
     WHERE db.dealership_tax_nr = d.Tax_Nr) AS dealership_brands,

    d.Addres AS dealership_address, 
    
    v.Make AS car_make, 
    v.Model AS car_model, 
    v.P_Year AS car_year, 
    v.Capacity AS car_capacity, 
    v.Power AS car_power, 
    v.Color AS car_color, 
    v.Price AS car_price, 

    c.C_Name AS client_name, 
    c.Email AS client_email, 
    c.Telephone AS client_phone, 
    p.Amount AS payment_amount
    

FROM
    agreement a
JOIN dealership d ON a.Tax_Nr = d.Tax_Nr
JOIN vehicle v ON a.VIN = v.VIN
JOIN client c ON a.EMBG = c.EMBG
LEFT JOIN payment p ON a.A_Id = p.A_Id
WHERE 
    a.Status = true;

Овој поглед е за клиенти кои имаат извршено уплата за 2 или повеќе возила, и би бил корисен како маркетинг алатка, да се испраќаат новости за нови огласи на тие корисници

CREATE OR REPLACE VIEW clients_with_2_or_more_vehicles AS
SELECT 
    c.EMBG AS client_embg,
    c.C_Name AS client_name,
    c.Email AS client_email,
    c.Telephone AS client_phone,
    COUNT(DISTINCT v.VIN) AS number_of_vehicles,
    SUM(p.Amount) AS total_paid_amount
    
FROM
    client c
JOIN agreement a ON c.EMBG = a.EMBG
JOIN vehicle v ON a.VIN = v.VIN
LEFT JOIN payment p ON a.A_Id = p.A_Id AND p.Amount > 0
GROUP BY 
    c.EMBG, c.C_Name, c.Email, c.Telephone
HAVING 
    COUNT(DISTINCT v.VIN) >= 2;