ADVANCED SQL TASK
Task 1
Retrieve order details with customer information
Query:
Select [Link], [Link], [Link], [Link], [Link],
[Link]
from orders o
left join customers c on [Link] = [Link]
Output:
Task 2
Find employee sales performance
Query:
SELECT [Link], [Link], [Link], COUNT([Link]) as totalnum,
sum([Link]) totalcred
FROM employees e
left join customers c on [Link] = [Link]
GROUP by [Link]
ORDER by totalnum DESC
Output:
Task 3
Identify High-Value Customers
Query:
SELECT [Link],SUM([Link]) as total
FROM customers c
inner join payments p on [Link] = [Link]
GROUP By [Link]
HAVING total > (
SELECT AVG([Link])
FROM payments p
)
ORDER By total ASC
Output:
Task 4
Calculate customer lifetime value
Query:
SELECT [Link] , sum([Link]) total
from customers c
INNER join payments p on [Link] = [Link]
GROUP by [Link]
order by total DESC
Output:
Task 5
Average Order Value per customer
Query:
with cte_table as (
select [Link],sum([Link]) / count([Link]) as ordervalue
from payments p
inner join orders o on [Link] = [Link]
inner join customers c on [Link] = [Link]
group by [Link]
)
select [Link],AVG(ordervalue) as avera
from cte_table,customers c
group by [Link]
Output:
Task 6
Number of orders per customers with order count categories
Query:
SELECT [Link],COUNT([Link]) as ordercount,
CASE
when COUNT([Link]) > 10 then 'High Frequency'
when COUNT([Link]) >= 5 then 'Regular'
Else 'Occasional'
end as customercategory
from customers c
inner join orders o on [Link] = [Link]
GROUP BY [Link]
order by ordercount ASC
Output: