SQl Sub Queries, temp Tables, Data Cleaning

Sub queries/Inner Queries/Nested queries

SELECT channel, AVG(events) AS average_events

FROM (SELECT DATE_TRUNC('day',occurred_at) AS day,

             channel, COUNT(*) as events

      FROM web_events 

      GROUP BY 1,2) sub

GROUP BY channel

ORDER BY 2 DESC;

with

WITH t1 AS (

  SELECT s.name rep_name, r.name region_name, SUM(o.total_amt_usd) total_amt

   FROM sales_reps s

   JOIN accounts a

   ON a.sales_rep_id = s.id

   JOIN orders o

   ON o.account_id = a.id

   JOIN region r

   ON r.id = s.region_id

   GROUP BY 1,2

   ORDER BY 3 DESC), 

t2 AS (

   SELECT region_name, MAX(total_amt) total_amt

   FROM t1

   GROUP BY 1)

SELECT t1.rep_name, t1.region_name, t1.total_amt

FROM t1

JOIN t2

ON t1.region_name = t2.region_name AND t1.total_amt = t2.total_amt;

Data Cleaning:

LEFT,

RIGHT,

COELASE,

CAST

CONCAT

POSITION