---finding the customers whose total spent is above average
---subqueries
select customer_id, sum(total_price) as customer_total_price
from orders_expanded
group by customer_id
HAVING sum(total_price) > (select avg(total_price)
from orders_expanded)
---CTES
with average_price as (
select avg(total_price) as avg_price
from orders_expanded
)
select customer_id, sum(total_price) as customer_total_price
from orders_expanded
group by customer_id
having sum(total_price) > (select avg_price from average_price)
----WINDOW FUNCTION
----ROW_NUMBER
select customer_id
first_name,
signup_date,
ROW_NUMBER() OVER ( PARTITION BY signup_date ORDER BY
signup_date) as row_number
from customers_expanded
----sum, avg, min, max, count
select customer_id,
order_date,
total _price,
sum(total_price) over (partition by customer_id order by
order_date) as running_total
from orders_expanded
order by customer_id, order_date
----average
select customer_id
order_date,
total_price,
avg(total_price) over (partition by customer_id) as avg_spent
from orders_expanded
---order by customer_id, order_date
---LAG FUNCTION
select customer_id,
order_date,
total_price,
LAG(total_price, 1) over (partition by customer_id order by
order_date) as
previous_order
from orders_expanded
-----LEAD WINDOW FUNCTION
select customer_id,
order_date,
total_price,
LEAD(total_price, 1) over (partition by customer_id order by
order_date) as
next_order
from orders_expanded
"5757.36"
"5007.03"
----FIRST VALUE AND LAST_VALUE WINDOW FUNCTIONS
with first_cte as (
select customer_id,
order_date,
total_price,
FIRST_VALUE(total_price) over (partition by customer_id order by
order_date) as
first_transaction
from orders_expanded
)
select customer_id,
order_date,
first_transaction - total_price as price_difference
from first_cte
----LAST VALUE WINDOW FUNCTION
SELECT customer_id,
order_date,
total_price,
last_value(total_price) over (partition by customer_id order by
order_date
ROWS BETWEEN UNBOUNDED PRECEEDING AND UNBOUNDED FOLLOWING ) AS
last_transaction
from orders_expanded
----CUME_DIST
SELECT ORDER_ID,
TOTAL_PRICE,
ORDER_ID,
CUME_DIST() OVER (ORDER BY ORDER_DATE) AS CUM_DIST
FROM ORDERS_EXPANDED
----RECAP ON WHAT WE HAVE DONE ALREADY
SELECT oe.order_id, oe.order_date, oe.product_name,ce.customer_id,
ce.country
from orders_expanded as oe
inner join customers_expanded as ce on oe.customer_id = ce.customer_id
where order_id is null
SELECT oe.order_id, oe.order_date, oe.product_name,ce.customer_id,
ce.country
from orders_expanded as oe
right join customers_expanded as ce on oe.customer_id = ce.customer_id
where order_id is null
SELECT oe.order_id, oe.order_date, oe.product_name,ce.customer_id,
ce.country
from orders_expanded as oe
full outer join customers_expanded as ce on oe.customer_id =
ce.customer_id
where order_id is null
---CTES
with total_spent_by_customers as (
select ce.customer_id,
ce.first_name,
ce.last_name,
sum(oe.total_price) as total_money_spent
from customers_expanded as ce
join orders_expanded oe
on ce.customer_id = oe.customer_id
group by ce.customer_id, ce.first_name, ce.last_name
)
select *
from total_spent_by_customers
order by total_money_spent desc
limit 10
----Multiple CTEs
with customer_amount_spent as (
select ce.customer_id,
ce.first_name,
ce.last_name,
ce.country,
sum(oe.total_price) as total_money_spent
from customers_expanded ce
join orders_expanded oe
on ce.customer_id = oe.customer_id
group by ce.customer_id, ce.country, ce.first_name, ce.last_name
)
,country_avg as (
select country , avg(total_money_spent) as avg_spent
from customer_amount_spent
group by country
)
select cas.customer_id,
cas.first_name,
cas. last_name,
cas.country,
cas.total_money_spent,
ca.avg_spent
from customer_amount_spent cas
join country_avg ca
using (country)
where cas.total_money_spent > ca.avg_spent
order by cas.country, cas.total_money_spent desc
----FIRST order per customer using CTEs
with first_orders as (
select customer_id,
sum(total_price),
min(order_date) as first_order_date
from orders_expanded
group by customer_id
)
select ce.customer_id,
ce.first_name,
fo.first_order_date
from customers_expanded ce
left join first_orders fo
using (customer_id)
order by fo.first_order_date
----DDLS
CREATE TABLE Students (
student_id INT PRIMARY KEY,
first_name VARCHART(50),
last_name VARCHART(50),
age INT,
enrollment_date DATE,
class VARCHART(50)
)
INSERT INTO Student_id
DEFAULT TABLE Courses_Student(
id INT PRIMARY KEY,
student_id INT FOREIGN KEY ,
course_id INT FOREIGN KEY
)
CREATE TABLE Courses(
course_id,
lecturer,
hall,
unit_load,
department
)
