CREATE TABLE favourite_color(id Serial, name TEXT, color TEXT);

INSERT INTO customers (NAME, email)
VALUES('Microsoft','hotline@microsoft.com') 
ON CONFLICT ON CONSTRAINT customers_name_key 
DO NOTHING;

INSERT INTO customers (name, email)
VALUES('Microsoft','hotline@microsoft.com') 
ON CONFLICT (name) 
DO 
   UPDATE SET email = EXCLUDED.email || ';' || customers.email;

WITH qb_plays AS (
  SELECT DISTINCT ON (POSITION, playid, gameid) POSITION, 
  playid, player_id, gameid 
  FROM tracking t 
  WHERE POSITION = 'QB'
),
total_qb_plays AS (
  SELECT count(*) play_count, player_id 
  FROM qb_plays
  GROUP BY player_id
),
qb_games AS (
	SELECT count(DISTINCT gameid) game_count, player_id 
    FROM qb_plays 
	GROUP BY player_id
),
sacks AS (
  SELECT count(*) sack_count, player_id 
  FROM play p
  INNER JOIN qb_plays ON p.gameid = qb_plays.gameid 
         AND p.playid = qb_plays.playid
  WHERE p.passresult = 'S'
  GROUP BY player_id
)
SELECT play_count, game_count, sack_count, 
   (sack_count/play_count::float)*100 sack_percentage, display_name 
FROM total_qb_plays tqp
INNER JOIN qb_games qg ON tqp.player_id = qg.player_id
LEFT JOIN sacks s ON s.player_id = qg.player_id
INNER JOIN player ON tqp.player_id = player.player_id
ORDER BY sack_count DESC NULLS last;

select extract(year from current_date) 
// 2021-10-20 -> 2021

SELECT *
FROM cars
WHERE
  (
    cars.min_price ≤ 13000 AND
    cars.min_price ≤ 15000 AND
    cars.max_price ≥ 13000 AND
    cars.max_price ≤ 15000
) OR (
    cars.min_price ≤ 13000 AND
    cars.min_price ≤ 15000 AND
    cars.max_price ≥ 13000 AND
    cars.max_price ≥ 15000
) OR (
    cars.min_price ≥ 13000 AND
    cars.min_price ≤ 15000 AND
    cars.max_price ≥ 13000 AND
    cars.max_price ≤ 15000
) OR (
    cars.min_price ≥ 13000 AND
    cars.min_price ≤ 15000 AND
    cars.max_price ≥ 13000 AND
    cars.max_price ≥ 15000
  )
ORDER BY cars.min_price;

CREATE MATERIALIZED VIEW sales_summary AS
  SELECT
      seller_no,
      invoice_date,
      sum(invoice_amt)::numeric(13,2) as sales_amt
    FROM invoice
    WHERE invoice_date < CURRENT_DATE
    GROUP BY
      seller_no,
      invoice_date
    ORDER BY
      seller_no,
      invoice_date;

CREATE UNIQUE INDEX sales_summary_seller
  ON sales_summary (seller_no, invoice_date);

REFRESH MATERIALIZED VIEW sales_summary;
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

INSERT INTO employees 
 (department_id, name, start_date, fingers, geom)
VALUES
 (1, 'Paul',   '2018/09/02', 10, 'POINT(-123.32977 48.40732)'),
 (1, 'Martin', '2019/09/02',  9, 'POINT(-123.32977 48.40732)'),
 (2, 'Craig',  '2019/11/01', 10, 'POINT(-122.33207 47.60621)'),
 (2, 'Dan',    '2020/10/01',  8, 'POINT(-122.33207 47.60621)');

SELECT row_to_json(employees)
FROM employees
WHERE employee_id = 1;

{
  "employee_id": 1,
  "department_id": 1,
  "name": "Paul",
  "start_date": "2018-09-02",
  "fingers": 10,
  "geom": {
    "type": "Point",
    "coordinates": [
      -123.329773,
      48.407326
    ]
  }
}

INSERT INTO employees 
 (department_id, name, start_date, fingers, geom)
VALUES
 (1, 'Paul',   '2018/09/02', 10, 'POINT(-123.32977 48.40732)'),
 (1, 'Martin', '2019/09/02',  9, 'POINT(-123.32977 48.40732)'),
 (2, 'Craig',  '2019/11/01', 10, 'POINT(-122.33207 47.60621)'),
 (2, 'Dan',    '2020/10/01',  8, 'POINT(-122.33207 47.60621)');

SELECT json_agg(e) 
FROM (
    SELECT employee_id, name 
    FROM employees
    WHERE department_id = 1
) e;

[
  {
    "employee_id": 1,
    "name": "Paul"
  },
  {
    "employee_id": 2,
    "name": "Martin"
  }
]

WITH 
-- strip down employees table
employees AS (
  SELECT department_id, name, start_date
  FROM employees
),
-- join to departments table and aggregate
departments AS (
  SELECT d.name AS department_name, 
         json_agg(e) AS employees
  FROM departments d
  JOIN employees e
  USING (department_id)
  GROUP BY d.name
)
-- output as one json list
SELECT json_agg(departments)
FROM departments;

[
  {
    "department_name": "cloud",
    "employees": [
      {
        "department_id": 2,
        "name": "Craig",
        "start_date": "2019-11-01"
      },
      {
        "department_id": 2,
        "name": "Dan",
        "start_date": "2020-10-01"
      }
    ]
  },
  {
    "department_name": "spatial",
    "employees": [
      {
        "department_id": 1,
        "name": "Paul",
        "start_date": "2018-09-02"
      }...

WITH rows AS (
  SELECT c.relname, a.attname, a.attnotnull, a.attnum, t.typname
  FROM pg_class c
  JOIN pg_attribute a 
    ON c.oid = a.attrelid and a.attnum >= 0
  JOIN pg_type t
    ON t.oid = a.atttypid
  JOIN pg_namespace n
    ON c.relnamespace = n.oid
  WHERE n.nspname = 'public'
    AND c.relkind = 'r'
),                                  
agg AS (     
  SELECT rows.relname, json_agg(rows ORDER BY attnum) AS attrs
  FROM rows
  GROUP BY rows.relname
)

-- finally we use json_object_agg to assign the json rows as to a key
SELECT json_object_agg(agg.relname, agg.attrs)
FROM agg;

{
  "departments": [
    {
      "relname": "departments",
      "attname": "department_id",
      "attnotnull": true,
      "attnum": 1,
      "typname": "int8"
    },
    {
      "relname": "departments",
      "attname": "name",
      "attnotnull": false,
      "attnum": 2,
      "typname": "text"
    }
  ],
  ...
}

/* A * by the name means it supports partial mode */

/* General-Purpose Aggregate Functions */
 avg*, bit_and*, bit_or*, bool_and*, bool_or*, count*, max*, min*, sum*, 
 array_agg, json_agg, json_object_agg,string_agg, xmlagg

/* Aggregate Functions for Statistics */
 corr* (correlation coefficient)
 covar_pop* (population covariance)
 stddev_samp* (standard deviation)
 regr_slope* (slope of the least-squares-fit)
 regr_count* (number of rows in which both inputs are non-null)
 ...

/* Ordered-Set Aggregate Functions */
 mode* (mode of a number set)
 percentile_cont (continuous percentile)
 percentile_disc (discrete percentile)
 
/* Hypothetical-Set Aggregate Functions */
 rank (rank ordered items with gaps) 1, 1, 3, 4, 4, 6
 dense_rank (rank ordered items with NO gaps) 1, 1, 2, 3, 3, 3, 4  
 percent_rank (rank ordered items with % 0-1)
 cume_dist (for calculating the probability of a value randomly being picked)

CREATE EXTENSION pg_trgm;

SELECT * FROM storm_events WHERE event_type % 'Drot' -- Similarity

SELECT DISTINCT(event_type) FROM storm_events WHERE DIFFERENCE(event_type, 'he') > 2

SELECT * FROM storm_events WHERE levenshtein(event_type, 'Drot') < 4

CREATE EXTENSION fuzzystrmatch; 

SELECT * 
from 
 generate_series(0,12,2);
-- (start, end, step)

 generate_series 
-----------------
               0
               2
               4
               6
               8
              10
              12

SELECT * 
from 
  generate_series(1,3) a, 
  generate_series(4,5) b;
-- (start, end, step)

a |b|
--+-+
 1|4|
 2|4|
 3|4|
 1|5|
 2|5|
 3|5|

SELECT user_id, first_order_time, next_order_time, id 
FROM (
    SELECT user_id, min(created_at) AS first_order_time 
    FROM orders 
    GROUP BY user_id
) o1
LEFT JOIN LATERAL (
    SELECT id, created_at AS next_order_time
    FROM orders
    WHERE user_id = o1.user_id AND created_at > o1.first_order_time
    ORDER BY created_at ASC 
    LIMIT 1
) o2 ON true;

SELECT
  extract(year FROM b.completed_at) AS yyyy,
  g.name,
  count(b.book_id) AS the_count
FROM
  book AS b
  INNER JOIN genre AS g 
          ON b.genre_id = g.genre_id
GROUP BY
  yyyy, g.name
ORDER BY
  yyyy DESC, g.name

SELECT
  extract(year FROM b.completed_at) AS yyyy,
  g.name,
  count(b.book_id) AS the_count
FROM book AS b
INNER JOIN genre AS g 
        ON b.genre_id = g.genre_id
GROUP BY 
  1, 2
ORDER BY
  1 DESC, 2

SELECT  country, product_name, 
        sum(amount_sold)
FROM    t_sales
GROUP BY GROUPING SETS ((1), (2))
ORDER BY 1, 2;

 country   | product_name | sum
-----------+--------------+-----
 Argentina |              | 137
 Germany   |              | 104
 USA       |              | 373
           | Hats         | 323
           | Shoes        | 291
(5 rows)

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)

BEGIN; -- start transaction
DELETE FROM
    queue_table
USING (
    SELECT * 
    FROM queue_table 
    LIMIT 10 
    FOR UPDATE 
    SKIP LOCKED
) q
WHERE q.id = queue_table.id 
RETURNING queue_table.*;
-- return table in using

SELECT timetable.add_job(
    'execute-func', 
    '5 0 * 8 *', 
    'SELECT public.my_func()'
);

SELECT timetable.add_job(
    'run-vacuum', 
    '23 0-20/2 * * *', 
    'VACUUM'
);

SELECT timetable.add_job(
    'refresh-matview', 
    '@every 2 hours', 
    'REFRESH MATERIALIZED VIEW 
               public.mat_view'
);

SELECT timetable.add_job(
    'clear-log', 
    '@reboot', 
    'TRUNCATE timetable.log'
);