WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ), top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

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 sum(salary) OVER w, avg(salary) OVER w
  FROM empsalary
  WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

CREATE TABLE invoice (
    invoice_no    integer        PRIMARY KEY,
    seller_no     integer,       -- ID of salesperson
    invoice_date  date,          -- date of sale
    invoice_amt   numeric(13,2)  -- amount of sale
);

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);

select 
    (select cls.relname from pg_class cls where cls.oid = cnstr.conrelid) as tbl, 
    (select array_agg(attname) from pg_attribute 
       where 
            attrelid = cnstr.conrelid and ARRAY[attnum] <@ cnstr.conkey) as fk_col, 
    (select cls.relname from pg_class cls where cls.oid = cnstr.confrelid) as target 
    from pg_constraint cnstr 
    where 
        cnstr.confrelid = (select oid from pg_class where relname = 'auth_user');

select table_name 
from information_schema.columns 
where 
    column_name ilike '%name%' and 
    table_name not ilike 'pg_%';

SELECT title
FROM pgweb
WHERE to_tsvector(title || ' ' || body) @@ to_tsquery('create & table')
ORDER BY last_mod_date DESC
LIMIT 10;

SELECT *
FROM crosstab(
  'SELECT
    a date,
    b.desc AS os,
    (random() * 10000 + 1)::int AS value
     FROM generate_series(
        (now() - ''100 days''::interval)::date, 
        now()::date, ''1 DAY''::interval) a,
        (SELECT unnest(ARRAY[''OSX'', ''Windows'', ''Linux'']) AS DESC) b 
     ORDER BY 1,2
  ','SELECT unnest(ARRAY[''OSX'', ''Windows'', ''Linux''])'
) 
AS ct(date date, OSX int, Windows int, Linux int);