USE OLAP
--which countries had sales on '2017-12-31'?

--Subquery
SELECT retailer_city FROM sales WHERE date = '2017-12-31'

--Main query, can't use =
SELECT distinct country
FROM countries
WHERE city = (SELECT retailer_city FROM sales WHERE date = '2017-12-31')

--Main query, can use IN
SELECT distinct country
FROM countries
WHERE city IN (SELECT retailer_city FROM sales WHERE date = '2017-12-31')

--Joining sales and country
SELECT c.country, s.date,s.quantity_sold,s.sale_price
FROM countries as c JOIN sales as s ON c.city=s.retailer_city

--How many countries and sales are in this list?
SELECT count(distinct city) as distinct_cities,count(*) as number_of_sales
FROM countries as c JOIN sales as s ON c.city=s.retailer_city

--How many countries in our countries table and how many sales in our sales table?
SELECT count(distinct city) as distinct_cities
FROM countries

SELECT count(*) as number_of_sales
FROM sales

--Missing cities?
SELECT city 
FROM countries
WHERE city NOT IN (SELECT retailer_city FROM sales)

--Missing sales? 
SELECT *
FROM sales
WHERE retailer_city NOT IN (SELECT city FROM countries)

--nulls
SELECT distinct city FROM countries

--exclude nulls from subquery
SELECT *
FROM sales
WHERE retailer_city NOT IN (SELECT city 
                            FROM countries 
							WHERE city is not null)

