<commit_before>-- generate days
CREATE OR REPLACE VIEW active_days AS
SELECT day FROM
(SELECT to_char(((SELECT min(outages.started_at) - '1 day'::interval FROM
outages)::date + (a ||' days')::interval), 'YYYY-MM-DD') as day from
generate_series(1,3650,1) a) as b
WHERE day::date <= now()::date + '1 day'::interval;

-- On sunday, 8-midnight
-- On saturday, 6-midnight
-- On weekdays, 4-midnight
CREATE MATERIALIZED VIEW bart_biz_hours AS
SELECT
  timestamptz (day || open_offset || ' US/Pacific')::timestamp as open,
  timestamptz (day || ' 24:00:00 US/Pacific')::timestamp as close,
  dow
FROM
(
SELECT
  day, dow,
  CASE WHEN dow = 0 THEN ' 08:00:00'
       WHEN dow = 6 THEN ' 06:00:00'
       ELSE ' 04:00:00'
  END as open_offset
FROM (SELECT day, date_part('dow'::text, day::date) as dow FROM active_days) as
dows) as offsets;

-- biz hour outages
CREATE VIEW bart_biz_hour_outages AS
  SELECT *, (close - open) AS duration FROM
    (SELECT outages.id, outages.elevator_id,
            GREATEST(bart_biz_hours.open, outages.started_at) AS open,
            LEAST(bart_biz_hours.close, outages.ended_at) AS close
     FROM outages
     JOIN elevators ON outages.elevator_id = elevators.id
     CROSS JOIN bart_biz_hours
     WHERE
       elevators.name NOT LIKE 'Muni%' AND
       (outages.started_at, COALESCE(outages.ended_at, NOW())) OVERLAPS (bart_biz_hours.open, bart_biz_hours.close)
    ) AS t ;

-- for a given elevator, sum the duration of its outages, and generate a
-- probabilty & percentage for each
 CREATE VIEW bart_biz_hour_elevator_outages AS
   SELECT elevator_name, elevator_id, station_id, sum,
   ROUND((EXTRACT(EPOCH from sum)/EXTRACT(EPOCH FROM total)*100)::decimal, 1) AS percentage,
   ROUND((EXTRACT(EPOCH from sum)/EXTRACT(EPOCH FROM total))::decimal, 3) AS probability
   FROM
    (SELECT elevators.name AS elevator_name, elevators.id AS elevator_id, station_id,
     SUM(duration) AS sum
     FROM elevators
     JOIN bart_biz_hour_outages ON elevator_id = elevators.id
     GROUP BY elevators.id
     ORDER BY elevators.station_id) AS u
   CROSS JOIN (SELECT SUM(close - open) AS total FROM bart_biz_hours) AS t;

-- singleton stations:
CREATE VIEW singleton_station_outages AS
  SELECT station_id, stations.name,
         probability,
         percentage,
         'singleton'::text AS station_type
  FROM bart_biz_hour_elevator_outages
  JOIN stations ON station_id = stations.id
  WHERE station_id IN (7, 19, 44, 41, 6, 29, 28, 15, 31, 23, 8, 36, 37, 12, 21);

-- series stations:
CREATE VIEW series_street_outages AS
  SELECT station_id, outages.* from outages
  JOIN elevators ON outages.elevator_id = elevators.id
  WHERE station_id IN (2, 3, 4, 9, 14, 18, 24, 27, 32, 34)
  AND   name LIKE '%Street%';

CREATE VIEW series_platform_outages AS
  SELECT station_id, outages.* from outages
  JOIN elevators ON outages.elevator_id = elevators.id
  WHERE station_id IN (2, 3, 4, 9, 14, 18, 24, 27, 32, 34)
  AND   name LIKE '%Street%';

CREATE VIEW series_overlapping_outages AS
  SELECT series_street_outages.station_id,
         GREATEST(series_street_outages.started_at,
                  series_platform_outages.started_at) AS open,
         LEAST(series_street_outages.ended_at,<commit_msg>Fixed bug in sql analytics<commit_after>-- generate days
CREATE OR REPLACE VIEW active_days AS
SELECT day FROM
(SELECT to_char(((SELECT min(outages.started_at) - '1 day'::interval FROM
outages)::date + (a ||' days')::interval), 'YYYY-MM-DD') as day from
generate_series(1,3650,1) a) as b
WHERE day::date <= now()::date + '1 day'::interval;

-- On sunday, 8-midnight
-- On saturday, 6-midnight
-- On weekdays, 4-midnight
CREATE MATERIALIZED VIEW bart_biz_hours AS
SELECT
  timestamptz (day || open_offset || ' US/Pacific')::timestamp as open,
  timestamptz (day || ' 24:00:00 US/Pacific')::timestamp as close,
  dow
FROM
(
SELECT
  day, dow,
  CASE WHEN dow = 0 THEN ' 08:00:00'
       WHEN dow = 6 THEN ' 06:00:00'
       ELSE ' 04:00:00'
  END as open_offset
FROM (SELECT day, date_part('dow'::text, day::date) as dow FROM active_days) as
dows) as offsets;

-- biz hour outages
CREATE VIEW bart_biz_hour_outages AS
  SELECT *, (close - open) AS duration FROM
    (SELECT outages.id, outages.elevator_id,
            GREATEST(bart_biz_hours.open, outages.started_at) AS open,
            LEAST(bart_biz_hours.close, outages.ended_at) AS close
     FROM outages
     JOIN elevators ON outages.elevator_id = elevators.id
     CROSS JOIN bart_biz_hours
     WHERE
       elevators.name NOT LIKE 'Muni%' AND
       (outages.started_at, COALESCE(outages.ended_at, NOW())) OVERLAPS (bart_biz_hours.open, bart_biz_hours.close)
    ) AS t ;

-- for a given elevator, sum the duration of its outages, and generate a
-- probabilty & percentage for each
 CREATE VIEW bart_biz_hour_elevator_outages AS
   SELECT elevator_name, elevator_id, station_id, sum,
   ROUND((EXTRACT(EPOCH from sum)/EXTRACT(EPOCH FROM total)*100)::decimal, 1) AS percentage,
   ROUND((EXTRACT(EPOCH from sum)/EXTRACT(EPOCH FROM total))::decimal, 3) AS probability
   FROM
    (SELECT elevators.name AS elevator_name, elevators.id AS elevator_id, station_id,
     SUM(duration) AS sum
     FROM elevators
     JOIN bart_biz_hour_outages ON elevator_id = elevators.id
     GROUP BY elevators.id
     ORDER BY elevators.station_id) AS u
   CROSS JOIN (SELECT SUM(close - open) AS total FROM bart_biz_hours) AS t;

-- singleton stations:
CREATE VIEW singleton_station_outages AS
  SELECT station_id, stations.name,
         probability,
         percentage,
         'singleton'::text AS station_type
  FROM bart_biz_hour_elevator_outages
  JOIN stations ON station_id = stations.id
  WHERE station_id IN (7, 19, 44, 41, 6, 29, 28, 15, 31, 23, 8, 36, 37, 12, 21);

-- series stations:
CREATE VIEW series_street_outages AS
  SELECT station_id, outages.* from outages
  JOIN elevators ON outages.elevator_id = elevators.id
  WHERE station_id IN (2, 3, 4, 9, 14, 18, 24, 27, 32, 34)
  AND   name LIKE '%Street%';

CREATE OR REPLACE VIEW series_platform_outages AS
  SELECT station_id, outages.* from outages
  JOIN elevators ON outages.elevator_id = elevators.id
  WHERE station_id IN (2, 3, 4, 9, 14, 18, 24, 27, 32, 34)
  AND   name NOT LIKE '%Street%';

CREATE VIEW series_overlapping_outages AS
  SELECT series_street_outages.station_id,
         GREATEST(series_street_outages.started_at,
                  series_platform_outages.started_at) AS open,
         LEAST(series_street_outages.ended_at,
{'fraction_non_alphanumeric': 0.09676425269645608, 'fraction_numerical': 0.031124807395993836, 'mean_word_length': 3.808148148148148, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4681901', 'n_tokens_mistral': 2560, 'n_tokens_neox': 2233, 'n_words': 680}