Marketing Reports

Marketing_Reports.sql

-- TASKS (using Postgres SQL)

-- TASK 1 
-- CTE: Common Table Expressions

-- TABLE: Customer
SET timezone = 'UTC';

CREATE TABLE IF NOT EXISTS Customers (
    id SERIAL PRIMARY KEY,
    name varchar(125) NOT NULL,
    service_address varchar(255) NOT NULL,
    email varchar(55) NOT NULL,
    phone integer NOT NULL,
    account_status varchar(12) DEFAULT Pending,
    customer_electricity_utility varchar(25) NOT NULL,
    customer_utility_number varchar(25) 
);

-- TABLE: Enrollments
CREATE TABLE IF NOT EXISTS Enrollments  (
id SERIAL PRIMARY KEY,
name varchar(125) NOT NULL,
marketing_channel varchar(25),
attempt_date timestamp without time zone default (now() at time zone 'utc'),
);


-- TASK 2
-- first attempt CTE
WITH _first_attempt AS (
SELECT e.*  FROM Enrollments e
JOIN(
    SELECT e2.name AS customer, 
        MIN(e2.attempt_date) AS first_contact,
        e2.marketing_channel
FROM Enrollments e2
GROUP BY 1 ) fe
ON fe.first_contact = e.attempt_date
ORDER BY 1)

-- latest attempt CTE
WITH _latest_attempt AS (
SELECT ex.*  FROM Enrollments ex
JOIN(
    SELECT el.name AS customer, 
        MAX(el.attempt_date) AS latest_contact,
        el.marketing_channel
FROM Enrollments el
GROUP BY 1 ) le
ON le.latest_contact = ex.attempt_date
ORDER BY 1
)


-- Task 2: Final Query
SELECT cust.id,
        fa.first_contact AS first_attempt,
        fa.marketing_channel AS first_channel,
        la.first_contact AS first_attempt,
        la.marketing_channel AS first_channel
FROM customers cust
JOIN _first_attempt ON cust.name = _first_attempt.customer 
JOIN _latest_attempt ON cust.name = _latest_attempt.customer


-- TASK 3 - marketing channel
--  Using CTE - 
WITH _mkt_last_attempt AS (
SELECT ex.*  FROM Enrollments ex
JOIN(
    SELECT el.marketing_channel, 
        MAX(el.attempt_date) AS latest_contact,
FROM Enrollments el
GROUP BY 1 ) le
ON le.latest_contact = ex.attempt_date
ORDER BY 1
)


WITH _mkt_first_attempt AS (
SELECT e.*  FROM Enrollments e
JOIN(
    SELECT e2.marketing_channel, 
        MIN(e2.attempt_date) AS first_contact,
        
FROM Enrollments e2
GROUP BY 1 ) fe
ON fe.first_contact = e.attempt_date
ORDER BY 1)


-- Task 3 - Final Query
SELECT marketing_channel, 
    SUM(marketing_channel) AS mkt_channel_count,
    _mkt_first_attempt.first_contact,
    _mkt_last_attempt.latest_contact
FROM _mkt_first_attempt
JOIN _mkt_last_attempt ON 
_mkt_first_attempt.marketing_channel = _mkt_last_attempt.marketing_channel;


-- TASK 4:
Not complete

Attempts to answer marketing dept.'s request for reports