Skip to content

Instantly share code, notes, and snippets.

@luishrd
Last active March 29, 2019 16:48
Show Gist options
  • Save luishrd/5d3da33d9a4d81891623ed8946d07113 to your computer and use it in GitHub Desktop.
Save luishrd/5d3da33d9a4d81891623ed8946d07113 to your computer and use it in GitHub Desktop.
select customers.CustomerName, customers.Country, orders.OrderId, orders.OrderDate
from orders inner join customers on orders.CustomerId = customers.CustomerId
select orders.OrderId, orders.OrderDate, employees.FirstName, employees.LastName
from orders
inner join employees on orders.employeeId = employees.employeeId
select orders.OrderId, orders.OrderDate, (employees.FirstName || ' ' || employees.LastName) as SoldBy
from orders
inner join employees on orders.employeeId = employees.employeeId
-- all customers that have no orders
select * from customers
-- inner join orders on customers.customerId = orders.customerId -- 196 records
left join orders on customers.customerId = orders.customerId -- 213 records
where orders.orderId is null
-- order by 1
select c.customerId, o.orderDate
from customers as c
left join orders as o
on c.customerId = o.customerId
where o.orderId is not null
select o.OrderId, o.OrderDate, c.CustomerName, s.ShipperName, e.FirstName
from orders as o
inner join customers as c on o.customerId = c.customerId
inner join employees as e on o.employeeId = e.employeeId
inner join shippers as s on s.shipperId = o.shipperId
-- how many items where ordered on each order
select o.orderId, count(*) as ItemsOrderedCount
from orders as o
inner join orderDetails as od on o.orderId = od.orderId
group by o.orderId
order by ItemsOrderedCount desc
-- revenue by product. hint: sum(), only the top 5
select p.ProductName, round(sum(od.quantity * p.price), 2) as Revenue
from products as p inner join orderDetails as od on p.productId = od.productId
group by p.ProductName
order by Revenue desc
-- limit 5
-- offset 5
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment