SQL_Queries_Udacity_PDSND_LIVE

-------------------------------------
------------SQL BASICS:--------------
-------------------------------------
SELECT
	occurred_at, account_id, channel
FROM
	web_events
LIMIT 15;
/*Display the 3 rows of table web_events and Limit the number of rows by 15*/
-----------------------------------------------
SELECT
	id, occurred_at, total_amt_usd
FROM
	orders
ORDER BY
	occurred_at
LIMIT 10;
/*Query the 3 named columns from the orders table. Get the 10 newest orders (ordered by date/time DESC)
/*Write a query to return the 10 earliest orders in the orders table. Include the id, occurred_at, and total_amt_usd.*/
-----------------------------------------------
SELECT
	id, account_id, total_amt_usd
FROM
	orders
ORDER BY
	total_amt_usd DESC
LIMIT 5;
/*Write a query to return the top 5 orders in terms of largest total_amt_usd. Include the id, account_id, and total_amt_usd.*/
----------------------------------------------
SELECT
	id, account_id, total_amt_usd
FROM
	orders
ORDER BY
	total_amt_usd
LIMIT 20;
/*Write a query to return the lowest 20 orders in terms of smallest total_amt_usd. Include the id, account_id, and total_amt_usd.*/
---------------------------------------------
--Compare 1
SELECT
	id, account_id, total_amt_usd
FROM
	orders
ORDER BY
	account_id, total_amt_usd DESC;
	/*Write a query that displays the order ID, account ID, and total dollar amount for all the orders,
	sorted first by the account ID (in ascending order), and then by the total dollar amount (in descending order).*/
---------------------------------------------
--Compare 2
SELECT
	id, account_id, total_amt_usd
FROM
	orders
ORDER BY
	total_amt_usd DESC, account_id;
/*Now write a query that again displays order ID, account ID, and total dollar amount for each order,
but this time sorted first by total dollar amount (in descending order), and then by account ID (in ascending order).*/
--------------------------------------------
--Comparsion between 1) and 2): The first query gives the 3 columns as an output, but the result is ordered by account_id first (ASC) and then by total_amt_usd (DESC) afterwards.
--That means, that you have the lowest account_id first and then the highest amount for that ID. But with the second query you have the largest amount first and then ordered by account_id.
--So you only have ordered by account_id (ASC) when you have 2 rows with the exact same total amount.
-------------------------------------------
SELECT
	*
FROM
	orders
WHERE
	gloss_amt_usd >= 1000
LIMIT 5;
/*Pulls the first 5 rows and all columns from the orders table that have a dollar amount of gloss_amt_usd greater than or equal to 1000.*/
-------------------------------------------
SELECT
	*
FROM
	orders
WHERE
	total_amt_usd < 500
LIMIT 10;
/*Pulls the first 10 rows and all columns from the orders table that have a total_amt_usd less than 500.*/
------------------------------------------
SELECT
	name, website, primary_poc
FROM
	accounts
WHERE name = 'Exxon Mobil';
/*Filter the accounts table to include the company name, website, 
and the primary point of contact (primary_poc) just for the Exxon Mobil company
 in the accounts table.*/
-----------------------------------------
SELECT
	id, account_id, (standard_amt_usd / standard_qty) AS unit_price
FROM 
	orders
LIMIT 10;

/*Create a column that divides the standard_amt_usd by the standard_qty to find the unit price for standard paper for each order.
Limit the results to the first 10 orders, and include the id and account_id fields. */
---------------------------------------
SELECT
	id, account_id, (poster_amt_usd / (poster_amt_usd + gloss_amt_usd + standard_amt_usd)) *100 AS revenue_poster_percentage
FROM
      orders
LIMIT 10;
/*Write a query that finds the percentage of revenue that comes from poster paper for each order.
You will need to use only the columns that end with _usd. (Try to do this without using the total column.) Display the id and account_id fields also.*/
---------------------------------------
SELECT
	name
FROM
	accounts
WHERE
	name LIKE 'C%'

--All the companies whose names start with 'C'. (LIKE)

SELECT
	name
FROM
	accounts
WHERE
	name LIKE '%one%'

--All companies whose names contain the string 'one' somewhere in the name.

SELECT
	name
FROM
	accounts
WHERE
	name LIKE '%s'

--All companies whose names end with 's'.

----------------------------------------------------
SELECT
	name, primary_poc, sales_rep_id
FROM
	accounts
WHERE name IN ('Walmart', 'Target', 'Nordstrom');

--Use the accounts table to find the account name, primary_poc, and sales_rep_id for Walmart, Target, and Nordstrom.

SELECT
	*
FROM
	web_events
WHERE channel IN ('organic', 'adwords');

--Use the web_events table to find all information regarding individuals who were contacted via the channel of organic or adwords.

------------------------------------------------------
SELECT
	name, primary_poc, sales_rep_id
FROM
	accounts
WHERE name NOT IN ('Walmart', 'Target', 'Nordstrom')

--Use the accounts table to find the account name, primary poc, and sales rep id for all stores except Walmart, Target, and Nordstrom.

SELECT
	*
FROM
	web_events
WHERE channel NOT IN ('organic', 'adwords');

--Use the web_events table to find all information regarding individuals who were contacted via any method except using organic or adwords methods.

SELECT
	name
FROM
	accounts
WHERE name NOT LIKE 'C%';

--All the companies whose names do not start with 'C'.

SELECT
	name
FROM
	accounts
WHERE name NOT LIKE '%one%';

--All companies whose names do not contain the string 'one' somewhere in the name.

SELECT
	name
FROM
	accounts
WHERE name NOT LIKE '%s';

--All companies whose names do not end with 's'.

SELECT
	*
FROM
	orders
WHERE
 	standard_qty > 1000 AND poster_qty = 0 AND gloss_qty = 0;

 --Write a query that returns all the orders where the standard_qty is over 1000, the poster_qty is 0, and the gloss_qty is 0.

 SELECT
	name
FROM
	accounts
WHERE name NOT LIKE 'C%' AND name LIKE '%s';

-- Using the accounts table, find all the companies whose names do not start with 'C' and end with 's'.

SELECT
	occurred_at, gloss_qty
FROM
	orders
WHERE
	gloss_qty BETWEEN 24 AND 29;

--When you use the BETWEEN operator in SQL, do the results include the values of your endpoints, or not?
--Figure out the answer to this important question by writing a query that displays the order date and gloss_qty data for all orders where gloss_qty is between 24 and 29.
--Then look at your output to see if the BETWEEN operator included the begin and end values or not.
-- Answer: The endpoints (24 AND 29) are in the results.

SELECT
	*
FROM
	web_events
WHERE channel IN ('organic', 'adwords') AND occurred_at BETWEEN '2016-01-01' AND '2016-12-31'
ORDER BY
	occurred_at DESC;

--Use the web_events table to find all information regarding individuals who were contacted via the organic or adwords channels
--and started their account at any point in 2016, sorted from newest to oldest.

SELECT
	id
FROM
	orders
WHERE
	gloss_qty > 4000 OR poster_qty > 4000;

--Find list of orders ids where either gloss_qty or poster_qty is greater than 4000. Only include the id field in the resulting table.

SELECT
	*
FROM
	orders
WHERE
	standard_qty = 0
AND
	(gloss_qty > 1000 OR poster_qty > 1000);

--Write a query that returns a list of orders where the standard_qty is zero and either the gloss_qty or poster_qty is over 1000.

SELECT
	*
FROM
	accounts
WHERE
	(name LIKE 'C%' OR name LIKE 'W%')
AND
	((primary_poc LIKE '%ana%' OR primary_poc LIKE '%Ana%') AND primary_poc NOT LIKE '%eana%');

--Find all the company names that start with a 'C' or 'W', and the primary contact contains 'ana' or 'Ana', but it doesn't contain 'eana'.


-------------------------------------
-------------SQL JOINS:--------------
-------------------------------------

--Udaycity 05.05.2019: Lesson 2 JOINs
--First JOIN to get the account information together with the order information:

SELECT
	*
FROM orders
JOIN accounts
ON	orders.account_id = accounts.id;

--Try pulling all the data from the accounts table, and all the data from the orders table.
SELECT
	*
FROM accounts
JOIN orders
ON accounts.id = orders.account_id;

--Try pulling standard_qty, gloss_qty, and poster_qty from the orders table,
--and the website and the primary_poc from the accounts table.

SELECT
	orders.standard_qty,
    orders.gloss_qty,
    orders.poster_qty,
    accounts.website,
    accounts.primary_poc
FROM orders
JOIN accounts
ON orders.account_id = accounts.id;

--Provide a table for all web_events associated with account name of Walmart.
--There should be three columns. 
--Be sure to include the primary_poc, time of the event, and the channel for each event. 
--Additionally, you might choose to add a fourth column to assure only Walmart events were chosen. 

SELECT
	a.primary_poc,
    we.occurred_at,
    we.channel,
    a.name
FROM web_events we
JOIN accounts a
ON we.account_id = a.id
WHERE a.name = 'Walmart';

--Provide a table that provides the region for each sales_rep along with their associated accounts.
--Your final table should include three columns: the region name, the sales rep name, and the account name.
--Sort the accounts alphabetically (A-Z) according to account name. 

SELECT
	r.name region,
    sr.name sales,
    a.name account
FROM sales_reps sr
JOIN region r
ON sr.region_id = r.id
JOIN accounts a
ON sr.id = a.sales_rep_id
ORDER BY a.name;

--Provide the name for each region for every order, 
--as well as the account name and the unit price they paid (total_amt_usd/total) for the order.
--Your final table should have 3 columns: region name, account name, and unit price.
--A few accounts have 0 for total, so I divided by (total + 0.01) to assure not dividing by zero.

SELECT
	r.name region,
    a.name account,
    (o.total_amt_usd/(o.total+0.01)) unit_price
FROM region r
JOIN sales_reps sr
ON r.id = sr.region_id
JOIN accounts a
ON sr.id = a.sales_rep_id
JOIN orders o
ON a.id = o.account_id

--Provide a table that provides the region for each sales_rep along with their associated accounts.
--This time only for the Midwest region. 
--Your final table should include three columns: the region name, the sales rep name, and the account name.
--Sort the accounts alphabetically (A-Z) according to account name.

SELECT
	r.name region,
    sr.name rep,
    a.name account
FROM region r
JOIN sales_reps sr
ON r.id = sr.region_id
JOIN accounts a
ON sr.id = a.sales_rep_id
WHERE r.name = 'Midwest'
ORDER BY a.name;

--Provide a table that provides the region for each sales_rep along with their associated accounts.
--This time only for accounts where the sales rep has a first name starting with S and in the Midwest region.
--Your final table should include three columns: the region name, the sales rep name, and the account name.
--Sort the accounts alphabetically (A-Z) according to account name. 

SELECT
	r.name region,
    sr.name rep,
    a.name account
FROM region r
JOIN sales_reps sr
ON r.id = sr.region_id
JOIN accounts a
ON sr.id = a.sales_rep_id
WHERE r.name = 'Midwest' AND sr.name LIKE 'S%'
ORDER BY a.name;

--Provide a table that provides the region for each sales_rep along with their associated accounts.
--This time only for accounts where the sales rep has a last name starting with K and in the Midwest region.
--Your final table should include three columns: the region name, the sales rep name, and the account name.
--Sort the accounts alphabetically (A-Z) according to account name.

SELECT
	r.name region,
    sr.name rep,
    a.name account
FROM region r
JOIN sales_reps sr
ON r.id = sr.region_id
JOIN accounts a
ON sr.id = a.sales_rep_id
WHERE r.name = 'Midwest' AND sr.name LIKE '% K%'
ORDER BY a.name;

--Provide the name for each region for every order,
--as well as the account name and the unit price they paid (total_amt_usd/total) for the order.
--However, you should only provide the results if the standard order quantity exceeds 100.
--Your final table should have 3 columns: region name, account name, and unit price.
--In order to avoid a division by zero error, adding .01 to the denominator here is helpful total_amt_usd/(total+0.01).


SELECT
	r.name region,
    a.name account,
    (o.total_amt_usd / (o.total)) unit_price
FROM region r
JOIN sales_reps sr
ON r.id = sr.region_id
JOIN accounts a
ON sr.id = a.sales_rep_id
JOIN orders o
ON a.id = o.account_id
AND o.standard_qty > 100;

--Provide the name for each region for every order, 
--as well as the account name and the unit price they paid (total_amt_usd/total) for the order. 
--However, you should only provide the results if the standard order quantity exceeds 100 
--and the poster order quantity exceeds 50. 
--Your final table should have 3 columns: region name, account name, and unit price. 
--Sort for the smallest unit price first. 
--In order to avoid a division by zero error, adding .01 to the denominator here is helpful (total_amt_usd/(total+0.01). 

SELECT
	r.name region,
    a.name account,
    (o.total_amt_usd / (o.total)) unit_price
FROM region r
JOIN sales_reps sr
ON r.id = sr.region_id
JOIN accounts a
ON sr.id = a.sales_rep_id
JOIN orders o
ON a.id = o.account_id
WHERE o.standard_qty > 100 AND o.poster_qty > 50
ORDER BY unit_price;

--Provide the name for each region for every order, 
--as well as the account name and the unit price they paid (total_amt_usd/total) for the order. 
--However, you should only provide the results if the standard order quantity exceeds 100 
--and the poster order quantity exceeds 50. 
--Your final table should have 3 columns: region name, account name, and unit price. 
--Sort for the largest unit price first. 
--In order to avoid a division by zero error, adding .01 to the denominator here is helpful (total_amt_usd/(total+0.01). 

SELECT
	r.name region,
    a.name account,
    (o.total_amt_usd / (o.total)) unit_price
FROM region r
JOIN sales_reps sr
ON r.id = sr.region_id
JOIN accounts a
ON sr.id = a.sales_rep_id
JOIN orders o
ON a.id = o.account_id
WHERE o.standard_qty > 100 AND o.poster_qty > 50
ORDER BY unit_price DESC;

--What are the different channels used by account id 1001? 
--Your final table should have only 2 columns: account name and the different channels. 
--You can try SELECT DISTINCT to narrow down the results to only the unique values.

SELECT DISTINCT
	a.name account,
    we.channel
FROM accounts a
JOIN web_events we
ON a.id = we.account_id
AND a.id = '1001'

--Find all the orders that occurred in 2015. 
--Your final table should have 4 columns: occurred_at, account name, order total, and order total_amt_usd.

SELECT
	o.occurred_at,
    a.name account,
    o.total total_quantity,
    o.total_amt_usd total_amount_usd
FROM orders o
JOIN accounts a
ON o.account_id = a.id
WHERE o.occurred_at BETWEEN '2015-01-01' AND '2015-12-31'
ORDER BY o.occurred_at DESC;

-------------------------------------
---------SQL AGGREGATIONS:-----------
-------------------------------------
COUNT():
--Count rows of all tables:
SELECT
	COUNT(*) row_count
FROM accounts;

--accounts: 	351
--orders:		6912
--region:		4
--sales_reps:	50
--web-events:	9073


--------------------------------------------------------------------------
SUM(), MIN(), MAX():
--Find the total amount of poster_qty paper ordered in the orders table.

SELECT
	SUM(poster_qty) poster_total_sales
FROM orders;


--Find the total amount of standard_qty paper ordered in the orders table.

SELECT
	SUM(standard_qty) standard_total_sales
FROM orders;

--Find the total dollar amount of sales using the total_amt_usd in the orders table.

SELECT
	SUM(total_amt_usd) sales_usd_total
FROM orders;

--Find the total amount spent on standard_amt_usd and gloss_amt_usd paper for each order in the orders table. This should give a dollar amount for each order in the table.
--NOTICE!!! INDIVIDUAL!!! SO NO AGGREGATION!!!

SELECT
	standard_amt_usd + gloss_amt_usd standard_gloss_amt_usd
FROM orders;


--Find the standard_amt_usd per unit of standard_qty paper. Your solution should use both an aggregation and a mathematical operator.

SELECT
	SUM(standard_amt_usd) / SUM(standard_qty) standard_unit_price
FROM orders;

--When was the earliest order ever placed? You only need to return the date.

SELECT
	MIN(occurred_at)
FROM orders;


--Try performing the same query as in question 1 without using an aggregation function. 

SELECT
	occurred_at
FROM orders
ORDER BY occurred_at
LIMIT 1;


--When did the most recent (latest) web_event occur?

SELECT
	MAX(occurred_at)	
FROM web_events;

--Try to perform the result of the previous query without using an aggregation function.

SELECT
	occurred_at	
FROM web_events
ORDER BY occurred_at DESC
LIMIT 1;

--------------------------------------------------------------------------
AVG():
--Find the mean (AVERAGE) amount spent per order on each paper type, as well as the mean amount of each paper type purchased per order. 
--Your final answer should have 6 values - one for each paper type for the average number of sales, as well as the average amount.

SELECT
	AVG(standard_amt_usd) mean_standard_usd,
    AVG(gloss_amt_usd) mean_gloss_usd,
    AVG(poster_amt_usd) mean_poster_usd,
    AVG(standard_qty) mean_standard,
    AVG(gloss_qty) mean_gloss,
    AVG(poster_qty) mean_poster
FROM orders;

--------------------------------------------------------------------------
MEDIAN:
--Via the video, you might be interested in how to calculate the MEDIAN. 
--Though this is more advanced than what we have covered so far try finding - what is the MEDIAN total_usd spent on all orders?
--MEDIAN CALC!!!!!!!!!!!!!!!!!!!

SELECT *
FROM (SELECT total_amt_usd
      FROM orders
      ORDER BY total_amt_usd
      LIMIT 3457) median_calc
ORDER BY total_amt_usd DESC
LIMIT 2;


--------------------------------------------------------------------------
GROUP BY I:
--Which account (by name) placed the earliest order? Your solution should have the account name and the date of the order.

SELECT
	a.name,
    o.occurred_at
FROM accounts a
JOIN orders o
ON a.id = o.account_id
ORDER BY o.occurred_at
LIMIT 1;

--Find the total sales in usd for each account. You should include two columns - the total sales for each company's orders in usd and the company name.

SELECT
	a.name,
	SUM(total_amt_usd) total_sales    
FROM orders o
JOIN accounts a
ON o.account_id = a.id
GROUP BY a.name
ORDER BY a.name;

--Via what channel did the most recent (latest) web_event occur, which account was associated with this web_event?
--Your query should return only three values - the date, channel, and account name.

SELECT
	we.occurred_at,
    we.channel,
    a.name
FROM web_events we
JOIN accounts a
ON we.account_id = a.id
ORDER BY occurred_at DESC;
LIMIT 1;

--Find the total number of times each type of channel from the web_events was used. 
--Your final table should have two columns - the channel and the number of times the channel was used.

SELECT
	channel,
	COUNT(*) amt_events	
FROM web_events
GROUP BY channel
ORDER BY amt_events DESC;

--Who was the primary contact associated with the earliest web_event? 

SELECT
    a.primary_poc
FROM accounts a
JOIN web_events we
ON a.id = we.account_id
ORDER BY we.occurred_at
LIMIT 1;

--What was the smallest order placed by each account in terms of total usd. Provide only two columns - the account name and the total usd. 
--Order from smallest dollar amounts to largest.

SELECT
	a.name account_name,
    MIN(total_amt_usd) smallest_order_usd
FROM orders o
JOIN accounts a
ON o.account_id = a.id
GROUP BY a.name
ORDER BY smallest_order_usd;



--Find the number of sales reps in each region. Your final table should have two columns - the region and the number of sales_reps. Order from fewest reps to most reps.

SELECT
	r.name,
	COUNT(sr.*) amt_sales_reps
FROM sales_reps sr
JOIN region r
ON sr.region_id = r.id
GROUP BY r.name
ORDER BY amt_sales_reps;

----------------------------------------------------------------------------------------------------------------------------------------------------
GROUP BY II:
--For each account, determine the average amount of each type of paper they purchased across their orders.
--Your result should have four columns - one for the account name and one for the average quantity purchased for each of the paper types for each account. 

SELECT
	a.name account_name,
    AVG(o.standard_qty) avg_standard,
    AVG(o.gloss_qty) avg_gloss,
    AVG(o.poster_qty) avg_poster
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY a.name;

--For each account, determine the average amount spent per order on each paper type. 
--Your result should have four columns - one for the account name and one for the average amount spent on each paper type.

SELECT
	a.name account_name,
    AVG(o.standard_amt_usd) avg_standard_usd,
    AVG(o.gloss_amt_usd) avg_gloss_usd,
    AVG(o.poster_amt_usd) avg_poster_usd
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY a.name;

--Determine the number of times a particular channel was used in the web_events table for each sales rep. 
--Your final table should have three columns - the name of the sales rep, the channel, and the number of occurrences. 
--Order your table with the highest number of occurrences first.

SELECT
	sr.name,
    we.channel,
	COUNT(we.*) num_events 
FROM web_events we
JOIN accounts a
ON we.account_id = a.id
JOIN sales_reps sr
ON a.sales_rep_id = sr.id
GROUP BY sr.name, channel
ORDER BY num_events DESC;

--Determine the number of times a particular channel was used in the web_events table for each region. 
--Your final table should have three columns - the region name, the channel, and the number of occurrences. 
--Order your table with the highest number of occurrences first.

SELECT
	r.name,
    we.channel,
	COUNT(we.*) num_events 
FROM web_events we
JOIN accounts a
ON we.account_id = a.id
JOIN sales_reps sr
ON a.sales_rep_id = sr.id
JOIN region r
ON sr.region_id = r.id
GROUP BY r.name, channel
ORDER BY num_events DESC;

----------------------------------------------------------------------------------------------------------------------------------------------------
SELECT DISTINCT:
--Use DISTINCT to test if there are any accounts associated with more than one region.

SELECT DISTINCT id, name
FROM accounts;


SELECT
	a.id account_id,
    r.id r_id,
    a.name account_name,
    r.name region_name
FROM accounts a
JOIN sales_reps sr
ON a.sales_rep_id = sr.id
JOIN region r
ON sr.region_id = r.id


--Have any sales reps worked on more than one account?

SELECT DISTINCT id, name
FROM sales_reps;

SELECT
	sr.id sales_rep_id,
	a.id account_id,
 	sr.name sales_rep,
 	a.name account_name
FROM sales_reps sr
JOIN accounts a
ON sr.id = a.sales_rep_id

----------------------------------------------------------------------------------------------------------------------------------------------------
HAVING:

--How many of the sales reps have more than 5 accounts that they manage?

SELECT
	sr.id sales_rep_id,
	sr.name sales_rep,
    COUNT(a.*) num_accounts
FROM sales_reps sr
JOIN accounts a
ON sr.id = a.sales_rep_id
GROUP BY sr.id, sr.name
HAVING COUNT(a.*) >5
ORDER BY num_accounts;


--How many accounts have more than 20 orders?

SELECT
	a.id account_id,
	a.name account_name,
    COUNT(o.*) num_orders
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY a.id, a.name
HAVING COUNT(o.*) > 20
ORDER BY num_orders;

--Which account has the most orders?

SELECT
	a.id account_id,
	a.name account_name,
    COUNT(o.*) num_orders
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY a.id, a.name
ORDER BY num_orders DESC
LIMIT 1;

--Which accounts spent more than 30,000 usd total across all orders?

SELECT
	a.id account_id,
	a.name account_name,
    SUM(o.total_amt_usd) total_sales
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY a.id, a.name
HAVING SUM(o.total_amt_usd) > 30000
ORDER BY total_sales;

--Which accounts spent less than 1,000 usd total across all orders?

SELECT
	a.id account_id,
	a.name account_name,
    SUM(o.total_amt_usd) total_sales
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY a.id, a.name
HAVING SUM(o.total_amt_usd) < 1000
ORDER BY total_sales;

--Which account has spent the most with us?

SELECT
	a.id account_id,
	a.name account_name,
    SUM(o.total_amt_usd) total_sales
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY a.id, a.name
ORDER BY total_sales DESC
LIMIT 1;

--Which account has spent the least with us?

SELECT
	a.id account_id,
	a.name account_name,
    SUM(o.total_amt_usd) total_sales
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY a.id, a.name
ORDER BY total_sales
LIMIT 1;

--Which accounts used facebook as a channel to contact customers more than 6 times?

SELECT
	a.id account_id,
	a.name account_name,
    we.channel,
    COUNT(we.*) num_events
FROM accounts a
JOIN web_events we
ON a.id = we.account_id
GROUP BY a.id, a.name, we.channel
HAVING we.channel = 'facebook'
AND COUNT(we.*) > 6
ORDER BY num_events;

--Which account used facebook most as a channel? 

SELECT
	a.id account_id,
    a.name account_name,
    we.channel,
    COUNT(we.*) num_events
FROM accounts a --WHERE we.channel = 'facebook' instead of HAVING
JOIN web_events we
ON a.id = we.account_id
GROUP BY a.id, a.name, we.channel
HAVING we.channel = 'facebook'
ORDER BY num_events DESC
LIMIT 1;

--Which channel was most frequently used by most accounts?

SELECT
	a.id account_id,
	a.name account_name,
    we.channel,
    COUNT(we.*) num_events
FROM accounts a
JOIN web_events we
ON a.id = we.account_id
GROUP BY a.id, a.name, we.channel
ORDER BY num_events DESC
LIMIT 10;

----------------------------------------------------------------------------------------------------------------------------------------------------
DATE-FUNCTIONS:
--Find the sales in terms of total dollars for all orders in each year, ordered from greatest to least. Do you notice any trends in the yearly sales totals?

SELECT
	DATE_PART('year',occurred_at) jahr,
    SUM(total_amt_usd) total_sales
FROM orders
GROUP BY 1
ORDER BY 2 DESC;

--Last year has a big drop in the amount of USD. Caused by time intervall
--2013 started in month 12 and 2017 only has month 1!

--Which month did Parch & Posey have the greatest sales in terms of total dollars? Are all months evenly represented by the dataset?

SELECT
	DATE_PART('month', occurred_at) monat,
    SUM(total_amt_usd) total_sales
FROM orders
WHERE occurred_at BETWEEN '2014-01-01' AND '2017-01-01'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;

--Which year did Parch & Posey have the greatest sales in terms of total number of orders? Are all years evenly represented by the dataset?

SELECT
	DATE_PART('year', occurred_at) jahr,
    COUNT(*) total_orders
FROM orders
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;

--2013 and 2017 have way less sales than the other years.

--Which month did Parch & Posey have the greatest sales in terms of total number of orders? Are all months evenly represented by the dataset?

SELECT
	DATE_PART('month', occurred_at) monat,
    COUNT(*) total_orders
FROM orders
WHERE occurred_at BETWEEN '2014-01-01' AND '2017-01-01'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;

--In which month of which year did Walmart spend the most on gloss paper in terms of dollars?

SELECT
	DATE_PART('year',o.occurred_at) jahr,
	DATE_PART('month',o.occurred_at) monat,
    SUM(o.gloss_amt_usd) gloss_total_usd,
    a.name account_name    
FROM orders o
JOIN accounts a
ON o.account_id = a.id
WHERE a.name = 'Walmart'
GROUP BY 1,2,4
ORDER BY 3 DESC
LIMIT 1;

--Alternative:

SELECT DATE_TRUNC('month', o.occurred_at) ord_date, SUM(o.gloss_amt_usd) tot_spent
FROM orders o 
JOIN accounts a
ON a.id = o.account_id
WHERE a.name = 'Walmart'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;

----------------------------------------------------------------------------------------------------------------------------------------------------
CASE:

--Create a column that divides the standard_amt_usd by the standard_qty to find the unit price for standard paper for each order.
--Limit the results to the first 10 orders, and include the id and account_id fields. NOTE - you will be thrown an error with the correct solution to this question.
--This is for a division by zero. You will learn how to get a solution without an error to this query when you learn about CASE statements in a later section.

--USE CASE INSTEAD OF THE WORKAROUND BEFORE!

SELECT
	id,
    account_id,
    CASE WHEN standard_qty = 0 OR standard_qty IS NULL THEN 0 
    ELSE standard_amt_usd / standard_qty END unit_price
FROM orders
LIMIT 10;

--Write a query to display for each order, the account ID, total amount of the order, and the level of the order - ‘Large’ or ’Small’ 
--- depending on if the order is $3000 or more, or smaller than $3000.

SELECT
	account_id,
    total_amt_usd,
    CASE WHEN total_amt_usd >= 3000 THEN 'Large'
    ELSE 'Small' END order_size
FROM orders o

--Write a query to display the number of orders in each of three categories, based on the 'total' amount of each order.
--The three categories are: 'At Least 2000', 'Between 1000 and 2000' and 'Less than 1000'.

SELECT
    CASE WHEN total >= 2000 THEN 'At Least 2000'
    WHEN total >= 1000 AND total < 2000 THEN 'Between 1000 and 2000'
    ELSE 'Less than 1000' END total_groups,
	COUNT(*) order_count
FROM orders
GROUP BY 1;

--We would like to understand 3 different levels of customers based on the amount associated with their purchases.
--The top level includes anyone with a Lifetime Value (total sales of all orders) greater than 200,000 usd.
--The second level is between 200,000 and 100,000 usd. The lowest level is anyone under 100,000 usd. 
--Provide a table that includes the level associated with each account. 
--You should provide the account name, the total sales of all orders for the customer, and the level. Order with the top spending customers listed first.

SELECT
	a.name account_name,
    SUM(total_amt_usd) total_spent,
    CASE WHEN SUM(total_amt_usd) > 200000 THEN 'top'
    WHEN SUM(total_amt_usd) > 100000 AND SUM(total_amt_usd) <= 200000 THEN 'middle'
    ELSE 'low' END account_level
FROM orders o
JOIN accounts a
ON o.account_id = a.id
GROUP BY a.name
ORDER BY 2 DESC;

--We would now like to perform a similar calculation to the first, but we want to obtain the total amount spent by customers only in 2016 and 2017.
--Keep the same levels as in the previous question. Order with the top spending customers listed first.

SELECT
	a.name account_name,
    SUM(o.total_amt_usd) total_spent,
    CASE WHEN SUM(o.total_amt_usd) > 200000 THEN 'top'
    WHEN SUM(o.total_amt_usd) > 100000 AND SUM(o.total_amt_usd) <= 200000 THEN 'middle'
    ELSE 'low' END account_level
FROM orders o
JOIN accounts a
ON o.account_id = a.id
WHERE occurred_at BETWEEN '2016-01-01' AND '2017-01-01'
GROUP BY 1
ORDER BY 2 DESC;

--We would like to identify top performing sales reps, which are sales reps associated with more than 200 orders.
--Create a table with the sales rep name, the total number of orders, and a column with top or not depending on if they have more than 200 orders.-
--Place the top sales people first in your final table.

SELECT
	sr.name,
    COUNT(o.*) num_orders,
    CASE WHEN COUNT(o.*) > 200 THEN 'top'
    ELSE 'not' END top_performing
FROM sales_reps sr
JOIN accounts a
ON sr.id = a.sales_rep_id
JOIN orders o
ON a.id = o.account_id
GROUP BY sr.name
ORDER BY num_orders DESC;

--The previous didn't account for the middle, nor the dollar amount associated with the sales.
--Management decides they want to see these characteristics represented as well. 
--We would like to identify top performing sales reps, which are sales reps associated with more than 200 orders or more than 750000 in total sales. 
--The middle group has any rep with more than 150 orders or 500000 in sales. 
--Create a table with the sales rep name, the total number of orders, total sales across all orders, and a column with top, middle, or low depending on this criteria.
--Place the top sales people based on dollar amount of sales first in your final table. You might see a few upset sales people by this criteria!

SELECT
	sr.name,
    COUNT(o.*) num_orders,
    SUM(total_amt_usd) total_spent,
    CASE WHEN COUNT(o.*) > 200 OR SUM(total_amt_usd) > 750000 THEN 'top'
    WHEN COUNT(o.*) > 150 OR SUM(total_amt_usd) > 500000 THEN 'middle'
    ELSE 'low' END sales_rep_level
FROM sales_reps sr
JOIN accounts a
ON sr.id = a.sales_rep_id
JOIN orders o
ON a.id = o.account_id
GROUP BY sr.name
ORDER BY num_orders DESC, total_spent DESC;