SQL Code Snippets

SELECT *  
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE COLUMN_NAME LIKE '%search%'  
ORDER BY TABLE_NAME;

select t.table_name
from information_schema.tables t
where t.table_schema = 'schema_name'  -- put schema name here
      and t.table_type = 'BASE TABLE'
order by t.table_name;

FROM information_schema.tables t
    JOIN information_schema.columns c 
           on c.table_name = t.table_name 
           and c.table_schema = t.table_schema

FROM information_schema.tables t
    JOIN information_schema.columns c USING(table_name, table_schema)

select t.table_schema,
       t.table_name
from information_schema.tables t
inner join information_schema.columns c 
           on c.table_name = t.table_name 
           and c.table_schema = t.table_schema
where c.column_name = 'colName'
      and t.table_schema not in ('information_schema', 'pg_catalog')
      and t.table_type = 'BASE TABLE'
order by t.table_schema;

SELECT 
    t.device
FROM 
    database.table t
GROUP BY t.device

SELECT 
    value_count_col
    ,COUNT(id_col) AS value_count
FROM 
    database.table t
GROUP BY 1
ORDER BY 2 DESC

SELECT 
    t.device
    ,COUNT(t.distance) AS value_count
FROM 
    database.table t
GROUP BY t.device
ORDER BY value_count DESC

CASE
    WHEN t.score BETWEEN 0  AND 24  THEN '0-24'
    WHEN t.score BETWEEN 25 AND 49  THEN '25-49'
    WHEN t.score BETWEEN 50 AND 74  THEN '50-74'
    WHEN t.score BETWEEN 75 AND 100 THEN '75-100'
    ELSE 'OTHER'
END AS score_group

SELECT
    ...
    ...
    FROM(
    SELECT
        ...
    ...
        ,RANK() OVER (PARTITION BY t.user_id ORDER BY t.created_at DESC) AS RANK
        FROM
            database.table AS t
    )
WHERE RANK = 1

SELECT
    ...
    , SUM(ltv_prod)  
      OVER (PARTITION BY policy_effective_month  
      ORDER BY calendar_month ASC ROWS UNBOUNDED PRECEDING) AS ltv_cumulative
    FROM
            database.table AS t
    )
WHERE RANK = 1

SELECT 
    score_decile, 
    COUNT(score_decile) AS count
FROM (
    SELECT
        *,
        CASE
            WHEN score < 10 THEN '0-9'
            WHEN score > 9 AND score < 20 THEN '10-19'
            WHEN score > 19 AND score < 30 THEN '20-29'
            WHEN score > 29 AND score < 40 THEN '30-39'
            WHEN score > 39 AND score < 50 THEN '40-49'
            WHEN score > 49 AND score < 60 THEN '50-59'
            WHEN score > 59 AND score < 70 THEN '60-69'
            WHEN score > 69 AND score < 80 THEN '70-79'
            WHEN score > 79 AND score < 90 THEN '80-89'
            WHEN score > 89 THEN '90-100'
        END as score_decile
    FROM database.table
)
GROUP BY score_decile
ORDER BY score_decile

SELECT 
    score_decile_approx, 
    COUNT(score_decile_approx) AS count
FROM (
    SELECT
        *,
        ROUND(score, -1) AS score_decile_approx
    FROM database.table
)
GROUP BY score_decile_approx
ORDER BY score_decile_approx

SELECT 
    score_decile_approx, 
    COUNT(score_decile_approx) AS count,
    RATIO_TO_REPORT(count) OVER () AS rate
FROM (
    SELECT
        *,
        ROUND(unmapped_score, -1) AS score_decile_approx
    FROM database.table

)
GROUP BY score_decile_approx
ORDER BY score_decile_approx

RATIOTOREPORT(accountcount) OVER(PARTITION BY DATETRUNC('month', account_timestamp))

    

DATETRUNC('quarter', accounttimestamp) AS x,

    RATIOTOREPORT(account_count) OVER(PARTITION BY x)

    

SELECT 
    COUNT(*), 
    MAX(created_at) 
FROM 
    database.table

SELECT 
    DATE_TRUNC('day', profile_timestamp) AS x,
    COUNT(DISTINCT account_id) AS new_user_count,
    AVG(new_user_count) OVER (ORDER BY x ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS count_rolling
FROM 
    database.table

CONCAT(ROUND((edwPLEmonths304 - edwPLEmonths30) / edwPLEmonths30 * 100, 2),'\%') AS percent_diff

    

earned_premium/SUM(earned_premium) OVER () as percent_of_total

RATIO_TO_REPORT(earned_premium) OVER () AS percent_of_total

WITH pre AS (  
    SELECT state  
         , amount  
         , month  
    FROM state_mgt.planned_rate  
)  
SELECT *  
FROM pre PIVOT(SUM(amount) FOR MONTH IN ('2022-02-28','2022-03-30','2022-04-30'));

SELECT DATEADD(DAY, -1, DATE_TRUNC('month', CURRENT_DATE - 1)) AS most_recent_month_end_date

-- create schema
CREATE SCHEMA my_schema;

-- create table
DROP TABLE IF EXISTS schema.table;
CREATE TABLE schema.table
    AS (
        SELECT * FROM table
    );

DELETE
FROM table
WHERE source = 'a';

INSERT INTO table
SELECT * FROM source_table

GRANT USAGE ON SCHEMA <name> TO user;
GRANT SELECT ON ALL TABLES IN SCHEMA <name TO user;

GRANT USAGE ON SCHEMA <name> TO GROUP readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA <name> TO GROUP readonly;

GRANT USAGE ON [schema] TO mode_analytics;
GRANT SELECT ON [schema.table] TO mode_analytics;

-- view tables in schema
SELECT t.table_name
FROM information_schema.tables t
WHERE t.table_schema = 'schema' -- put schema name here
    AND t.table_type = 'BASE TABLE'
ORDER BY t.table_name;

SELECT
    col
    LAG(col, 1) OVER (ORDER BY calendar_month ASC) AS col_lag_7

, DATEDIFF('month', policy_effective_month, calendar_month) AS dev_month  
, dev_month / 6 + 1 AS term

SELECT 
    *
FROM 
    database.table t
WHERE
    t.created_at > GETDATE() - INTERVAL '7 days'

DATE_DIFF('month', start_month, calendar_month) AS age

SELECT
    DATE_TRUNC('day', timestamp) AS day

SELECT
    DATE_TRUNC('week', timestamp) AS week

SELECT
    DATE_TRUNC('month', timestamp) AS month

SELECT
    DATE_TRUNC('quarter', timestamp) AS quarter

policy_inception_month = LAST_DAY(DATEADD(MM,-6, CURRENT_DATE))

WHERE prediction_date = LAST_DAY(DATE_ADD('month', -1, CURRENT_DATE))

SELECT LEFT(GETDATE(),10)