-- filter customers who have id greater than 21000 and they live in postcode 1429 
-- and their name stars with e

select customerid id, name customername, lower(name) convertedname,  dob, postcode zipcode 
from customer
where
customerid > 21000 AND postcode = '1429' AND name like '%e%'

--create a dimension table postcode

select  postcode from customer group by postcode

select distinct  postcode from customer 

--customers and what they purchased, for how much and when

create view v_customerpurchaseoverview as

select name, postcode, city, purchasedate, st.transactionid, finalprice, si.categoryid, categoryname
from customer c
 join saletransaction st on c.customerid = st.customerid
join saleitem si on st.transactionid = si.transactionid
join categorymap cm on si.categoryid = cm.categorymapid
join maincategory mainc on cm.maincategoryid = mainc.categoryid

select * from v_customerpurchaseoverview

--total revenue generated by eachpost code

select postcode, sum(finalprice) totalrev
from customer c
join saletransaction st on c.customerid = st.customerid
join saleitem si on st.transactionid = si.transactionid
join categorymap cm on si.categoryid = cm.categorymapid
join maincategory mainc on cm.maincategoryid = mainc.categoryid
group by postcode

--
--total rev by each category

select postcode, categoryname, sum(finalprice) totalrev
from customer c
join saletransaction st on c.customerid = st.customerid
join saleitem si on st.transactionid = si.transactionid
join categorymap cm on si.categoryid = cm.categorymapid
join maincategory mainc on cm.maincategoryid = mainc.categoryid
group by postcode, categoryname
order by postcode


create view v_yearlysale as
select date_part('year',purchasedate) yearofsale,
sum(finalprice) totalrevenue
from customer c
join saletransaction st on c.customerid = st.customerid
join saleitem si on st.transactionid = si.transactionid
join categorymap cm on si.categoryid = cm.categorymapid
join maincategory mainc on cm.maincategoryid = mainc.categoryid
group by date_part('year',purchasedate)

select * from v_yearlysale