cte_example.sql

SELECT
    r.id as restaurant_id,
    rez_sheet.shift as shift,
    start AT TIME ZONE r.timezone as start_time,
    "end" AT TIME ZONE r.timezone as end_time
FROM
    rez_sheet,rez_schedule rs,rez_restaurant r
WHERE
    r.id = rez_sheet.restaurant_id AND
    rs.sheet_id = rez_sheet.id;

cte_example_2.sql

WITH scheduled_shifts AS (
    -- The same query above; referenced as scheduled_shifts
    -- in the main query
    SELECT
        r.id as restaurant_id,
        rez_sheet.shift as shift,
        start AT TIME ZONE r.timezone as start_time,
        "end" AT TIME ZONE r.timezone as end_time
    FROM
        rez_sheet,rez_schedule rs,
        rez_restaurant r
    WHERE
        r.id = rez_sheet.restaurant_id AND
        rs.sheet_id = rez_sheet.id
)
SELECT
    count(r.id) as parties,
    sum(r.covers) as people,
    ss.shift as shift,
    ss.restaurant_id as restaurant_id
FROM
    -- This references the query in the body of the WITH statement
    -- as if it were a typical table join
    scheduled_shifts ss
JOIN
    rez_reservation r
ON (
    ss.restaurant_id = r.restaurant_id AND
    r.when::time between ss.start_time and ss.end_time
)
GROUP BY 3,4;

ntile_example.sql

with user_signup_counts as (
    -- Query that produces 1 row per user, with the number
    -- of reviews that user has created over all time, their
    -- country and signup date.
)
select distinct
    ntile(100) OVER (
        PARTITION BY signup_country 
        ORDER BY review_count) 
   as percentile,
   review_count,
   signup_country,
   quarter
FROM
   user_signup_counts;

window_function_example.sql

WITH counts_per_res_and_city as (
    SELECT DISTINCT
        rez_restaurant.id as restaurant_id,
        rez_restaurant.locality as city,
        count(r.id) OVER (PARTITION BY rez_restaurant.id) as per_restaurant,
        count(r.id) OVER (PARTITION BY rez_restaurant.locality) as per_city
    FROM
        rez_restaurant JOIN 
        rez_reservation r ON (
            rez_restaurant.id = r.restaurant_id
    )
)
SELECT restaurant_id, 
    city, 
    ((per_restaurant * 100)/per_city) as restaurant_city_percent
FROM counts_per_res_and_city;