generates list of parameters to sync balance sheets, to be called against this endpoint: https://iexcloud.io/docs/api/#balance-sheet

balance_sheet_parameters_tofetch.sql

WITH dates AS (
    SELECT
        date_trunc('day', dd):: date AS date
    FROM generate_series
        ('2017-01-01'::timestamp
        , @currentDate::timestamp
        , '1 day'::interval) dd
),

     quarters AS (
         SELECT
             min(date)                     quarter_start_date,
             max(date)                     quarter_end_date,
             date_part('quarter', date) AS quarter,
             date_part('year', date)    AS year
         FROM dates
         GROUP BY quarter, year
         ORDER BY year DESC, quarter DESC
     ),

     existing_balance_sheets AS (
         SELECT
             bs."Symbol" AS symbol,
             q.quarter,
             q.year
         FROM balance_sheets bs
         JOIN quarters q
              ON bs.report_date BETWEEN q.quarter_start_date AND q.quarter_end_date
         WHERE bs."Symbol" = ANY (@symbols)
     ),

     wanted_balance_sheets AS (
         SELECT
             symbol,
             q.quarter,
             q.year
         FROM companies c
         CROSS JOIN quarters q
         WHERE c.symbol = ANY (@symbols)
     ),

     balance_sheet_fetch_params AS (
         SELECT
             symbol,
             quarter,
             year
         FROM wanted_balance_sheets wbs
         WHERE NOT exists(
                 SELECT *
                 FROM existing_balance_sheets ebs
                 WHERE wbs.symbol = ebs.symbol
                   AND wbs.quarter = ebs.quarter
                   AND wbs.year = ebs.year
             )
     ),

     balance_sheet_fetch_params_ranked AS (
         SELECT *,
                rank() OVER (PARTITION BY symbol ORDER BY year, quarter) AS daterank
         FROM balance_sheet_fetch_params
     ),

     oldest_balance_sheet_fetch_params AS (
         SELECT
             symbol,
             quarter,
             year
         FROM balance_sheet_fetch_params_ranked
         WHERE daterank = 1
     ),

     all_balance_sheet_fetch_params AS (
         SELECT
             w.symbol,
             w.quarter,
             w.year
         FROM oldest_balance_sheet_fetch_params o
         JOIN wanted_balance_sheets w
              ON o.symbol = w.symbol
                  AND cast(CAST(o.year AS text) || '.' || CAST(o.quarter AS text) AS NUMERIC) <=
                      cast(CAST(w.year AS text) || '.' || CAST(w.quarter AS text) AS NUMERIC)
     ),

     symbols_parameters AS (
         SELECT
             symbol,
             least(count(*), 12) AS last
         FROM all_balance_sheet_fetch_params
         GROUP BY symbol
     ),

     latest_balance_report_date AS (
         SELECT
             "Symbol" AS      symbol,
             max(report_date) latest_report_date
         FROM balance_sheets
         GROUP BY "Symbol"
     ),

     latest_fetch_attempts AS (
         SELECT
             symbol,
             max(date) AS latest_date
         FROM fetch_attempts
         WHERE fetch_type = 'balance_sheet'
         GROUP BY symbol
     ),

     symbols_parameters_joined AS (
         SELECT
             sp.symbol,
             sp.last,
             lbrd.latest_report_date,
             lfa.latest_date AS latest_fetch_attempt_date
         FROM symbols_parameters sp
         LEFT OUTER JOIN latest_balance_report_date lbrd
                         ON sp.symbol = lbrd.symbol
         LEFT OUTER JOIN latest_fetch_attempts lfa
                         ON sp.symbol = lfa.symbol
     )

SELECT
    spj.symbol,
    spj.last,
    spj.latest_report_date,
    now()::date - latest_report_date::date        last_report_date_age,
    spj.latest_fetch_attempt_date,
    now()::date - latest_fetch_attempt_date::date last_fetch_attempt_date_age
FROM symbols_parameters_joined spj
WHERE NOT (last = 1 AND latest_report_date IS NOT NULL AND (now()::date - latest_report_date::date) <= 95)
  AND NOT (last = 1 AND latest_fetch_attempt_date IS NOT NULL AND (now()::date - latest_fetch_attempt_date::date) <= 10)
ORDER BY spj.latest_report_date