sql

 https://www.youtube.com/watch?v=Td-cmLfQ7uU&ab_channel=TinaHuang

 01:40

building a potential and reasonable schema

 02:28

-- is a comment in SQL

 03:06

FROM -> JOIN -> ON … AND

 04:21

line 13: build a function by using SELECT

 04:34

COUNT (DISTINCT …)

1. joining all iOS and web users by ID
2. func DATE_TRUNC
3. num_users = distinct count by 'day'
4. group the result into num_users and day

CREATE TABLE

INSERT INTO
VALUES
(now()), INTERVAL (time)

 https://www.youtube.com/watch?v=d2920ysu2hQ&ab_channel=JayFeng

 01:37

peepoThink (1)

• we could use GROUP BY to see how many posting share the same uid
• we could then SELECT WHERE count == 1
  (2)
• use DATE_TRUNC to group by year or
• use WHERE to split each into 180 day intervals from now()

 02:13

visualize the output and work backwards, make an output table, split into intermediate tables to process

 05:55

WITH user_job AS (
	SELECT u_id, j_id, COUNT(DISTINCT date_posted) AS num_posted
	FROM jobs
	GROUP BY u_id, j_id
)

 08:06

SELECT
	SUM(CASE WHEN avg_posted > 1 THEN 1 END) AS posted_multiple
	, SUM(CASE WHEN avg_posted = 1 THEN 1 END) AS posted_once
FROM (
	SELECT
		user_id, AVG(num_posted) AS avg_posted
	FROM user_job
	GROUP BY user_id
) AS t

 10:41

HAVING won't work because it will filter values

Post Mortem

WITH - named function
COUNT(DISTINCT field_name) - get count of distinct values in field
GROUP_BY creates sub tables on different keys
CASE WHEN - if
END - end the if
ELSE - else
AVG() - get average of field

Use intermediate tables to get to the final table

 <https://www.youtube.com/watch?v=f1vuGPuug9s&ab_channel=DatabaseStar>

03:02

use the FROM clause to understand tables in the query

 04:60

LEFT JOIN (only join items into the union from the left table) uses a subquery where we can SELECT inside and the JOIN it on a field to the computed table

 08:50

any filters? HAVING is filtering where sum values != 0

 14:34

Run the main query in parts

Post Mortem