kerbelp

#!/usr/bin/env python

class Node:

    def __init__(self, value):
        self.value = value
        self.right = None
        self.left = None

    def is_valid_bst(self, head=None):

kerbelp

CREATE OR REPLACE VIEW admin.v_encoded_columns_percentage 
AS
SELECT def.tablename,
       def.mbytes,
       SUM(CASE WHEN def.attencodingtype = 0 THEN 0 ELSE 1 END)::double precision / COUNT(DISTINCT def.attname)::double precision AS encoded_percentage
FROM (SELECT n.nspname AS schemaname,
             c.oid,
             c.relname AS tablename,
             format_encoding(a.attencodingtype) AS "encoding",
             a.attencodingtype,

kerbelp

CREATE OR REPLACE VIEW admin.v_users_groups 
AS
SELECT pg_group.groname as group_name,
       pg_user.usename as user_name,
       pg_user.usesuper as is_superuser
FROM pg_group,
     pg_user
WHERE pg_user.usesysid = ANY (pg_group.grolist);

kerbelp

CREATE OR REPLACE VIEW admin.v_vacuum_jobs_summary
AS 
 SELECT DISTINCT us.usename, perm.name, vac.eventtime, trunc(vac.eventtime) AS date, vac.status, vac."rows", vac.sortedrows
   FROM stl_vacuum vac
   JOIN stv_tbl_perm perm ON perm.id = vac.table_id
   JOIN pg_user us ON us.usesysid = vac.userid
  WHERE trunc(vac.eventtime) >= trunc('now' - 1)
  ORDER BY vac.eventtime DESC;

kerbelp

SELECT stv_tbl_perm.db_id,
       stv_tbl_perm.id,
       stv_tbl_perm.name,
       SUM(stv_tbl_perm. "rows") AS "rows",
       SUM(stv_tbl_perm. "rows") - SUM(stv_tbl_perm.sorted_rows) AS unsorted_rows
FROM stv_tbl_perm
GROUP BY stv_tbl_perm.db_id,
         stv_tbl_perm.id,
         stv_tbl_perm.name;

kerbelp

CREATE OR REPLACE VIEW admin.v_tables_to_vacuum 
AS
SELECT (('VACUUM FULL ' || derived_table1.schemaname) || '.') || derived_table1.tablename AS query
FROM (SELECT BTRIM(pgdb.datname) AS dbase_name,
             BTRIM(pgn.nspname) AS schemaname,
             BTRIM(a.name) AS tablename,
             a.id AS tbl_oid,
             b.mbytes AS megabytes,
             a. "rows" AS rowcount,
             a.unsorted_rows AS unsorted_rowcount,

kerbelp

SELECT TRIM(pgdb.datname) AS DATABASE,
         TRIM(pgn.nspname) AS SCHEMA,
         TRIM(a.name) AS TABLE,
         b.mbytes,
         a.rows
  FROM (SELECT db_id,
               id,
               name,
               SUM(ROWS) AS ROWS
        FROM stv_tbl_perm a

kerbelp

SELECT SUM(capacity) FROM stv_partitions;

kerbelp

CREATE OR REPLACE VIEW admin.v_space_by_schema
AS
WITH CAPACITY AS
(
  SELECT SUM(capacity) FROM stv_partitions
),
USAGE AS
(
  SELECT TRIM(pgdb.datname) AS DATABASE,
         TRIM(pgn.nspname) AS SCHEMA,

kerbelp

WITH total_count_calc AS
(
  SELECT COUNT(*) AS total_count
  FROM elb_logs_raw_partition
  WHERE YEAR = '2016'
  AND   MONTH = '12'
  AND   DAY = '15'
),
status_count_calc AS
(