Final-PostgreSQL-file

-- some data cleaning on the table
--
-- in the comchannel column in some cells, SMS has been shortened to S
-- Email has been shortened to E
-- and Phone has been shortened to P
-- this update block updates them to their complete values
UPDATE customer
SET comchannel = CASE
        WHEN comchannel = 'S' THEN 'SMS'
        WHEN comchannel = 'E' THEN 'Email'
        WHEN comchannel = 'P' THEN 'Phone'
        else comchannel
    END;
-- in the gender column in some cells, female has been shortened to f
-- and male has been shortened to m. This update block updates them
-- to their full values
UPDATE customer
SET Gender = CASE
        WHEN Gender = 'f' THEN 'female'
        WHEN Gender = 'm' THEN 'male'
        else Gender
    END;
-- create analytics base table
-- c is an alias for customer table
-- mp is an alias for motor_policies table
-- tp is an alias for travel_policies table
-- hp is an alias for health_policies table
-- alias is used because some column names are same
-- across the table and selecting those column by referencing
-- using alias helps avoid any conflicts
CREATE TABLE IF NOT EXISTS abt AS
SELECT c.customerid,
    c.givenname,
    c.surname,
    c.Gender,
    c.CardType,
    c.location,
    c.comchannel,
    c.motorid,
    mp.MotorType,
    mp.policystart AS mp_policystart,
    mp.policyend AS mp_policyend,
    c.healthid,
    hp.HealthType,
    hp.policystart AS hp_policystart,
    hp.policyend AS hp_policyend,
    c.travelid,
    tp.TravelType,
    tp.policystart AS tp_policystart,
    tp.policyend AS tp_policyend
    FROM customer c
    LEFT JOIN motor_policies mp ON c.motorid = mp.motorid
    LEFT JOIN health_policies hp ON c.healthid = hp.healthid
    LEFT JOIN travel_policies tp ON c.travelid = tp.travelid;
-- data analysis on the abt table, which groups the table
-- by Location and comchannel and gives the count for each
-- group
SELECT abt.location,
    comchannel,
    count(customerid)
FROM abt
GROUP BY abt.location,
    comchannel
ORDER BY abt.location,
    comchannel;
-- data analysis on the abt table, which groups the table
-- by Gender and comchannel and gives the count for each
-- group
SELECT Gender,
    comchannel,
    count(customerid)
FROM abt
GROUP BY Gender,
    comchannel
ORDER BY Gender,
    comchannel;
-- data analysis on the abt table, which groups the table
-- by CardType and comchannel and gives the count for each
-- group
SELECT CardType,
    comchannel,
    count(customerid)
FROM abt
GROUP BY CardType,
    comchannel
ORDER BY CardType,
    comchannel;
-- data analysis on the abt table, which groups the table
-- by CardType and motorpolicy type and gives the count for each
-- group
SELECT CardType,
    MotorType,
    count(customerid)
FROM abt
GROUP BY CardType,
    MotorType
    ORDER BY CardType,
    MotorType;