Use Cases

Find the performance of a company's revenue segments over time

View Target's quarterly category mix over time.

WITH target_category_segments AS (
    SELECT
        company_name,
        business_segment,
        date,
            value AS segment_value
    FROM cybersyn.sec_metrics_timeseries
    WHERE 
        cik = '0000027419'
        AND measure = 'REVENUE'
        AND frequency = 'QUARTERLY' 
        AND business_segment IN ('HARDLINES','HOME FURNISHINGS AND DECOR','APPAREL AND ACCESSORIES','BEAUTY AND HOUSEHOLD ESSENTIALS','FOOD AND BEVERAGE')
)

, quarterly_performance AS (
    SELECT
        company_name,
        date,
        SUM(segment_value) AS quarterly_total_value
    FROM target_category_segments
    GROUP BY 1,2
)

SELECT *, segment_value / quarterly_total_value AS category_mix
FROM target_category_segments
JOIN quarterly_performance
USING (company_name, date)
ORDER BY date, business_segment;

Find the performance of Darden Restaurants' brands over time.

SELECT *
FROM cybersyn.sec_metrics_timeseries
WHERE 
    cik = '0000940944'
    AND frequency = 'QUARTERLY'
    AND business_segment IN ('OLIVE GARDEN','LONGHORN STEAKHOUSE','FINE DINING','ALL OTHER');

View values as of a specific date and time

Use Cybersyn’s point-in-time history table to look back at what FRED data was published as of October 31, 2023.

SELECT *
FROM cybersyn.bureau_of_labor_statistics_employment_timeseries_history
WHERE _effective_start_timestamp <= '2023-10-31 00:00:00'
  AND (_effective_end_timestamp >= '2023-10-31 00:00:00' OR _effective_end_timestamp IS NULL);

Pull all historical revisions to a particular data point

Check the history of reported nonfarm employees in the United States for a given date. Pull historical estimates that may have been revised in subsequent monthly releases.

SELECT *
FROM cybersyn.financial_fred_timeseries_history
WHERE variable_name = 'All Employees, Total Nonfarm, Seasonally adjusted, Monthly, Persons'
  AND date = '2023-09-30'
ORDER BY _effective_start_timestamp;

Get timestamps for all updates to Cybersyn’s data

Select all distinct times any new data was inserted or any existing data was modified in the FX rate table. This will create a log of all timestamps where Cybersyn ran a job that made any changes to the underlying data in the table.

SELECT DISTINCT _effective_start_timestamp
FROM cybersyn.fx_rates_timeseries_history
ORDER BY _effective_start_timestamp;

FDIC deposit exposure

Determine which banks have the highest percentage of uninsured deposits.

WITH big_banks AS (
    SELECT id_rssd
    FROM cybersyn.financial_institution_timeseries
    WHERE variable = 'ASSET'
      AND date = '2022-12-31'
      AND value > 1E10
)
SELECT name,
       1 - value AS pct_uninsured,
       ent.is_active
FROM cybersyn.financial_institution_timeseries AS ts
INNER JOIN cybersyn.financial_institution_attributes AS att ON (ts.variable = att.variable)
INNER JOIN cybersyn.financial_institution_entities AS ent ON (ts.id_rssd = ent.id_rssd)
INNER JOIN big_banks ON (big_banks.id_rssd = ts.id_rssd)
WHERE ts.date = '2022-12-31'
  AND att.variable_name = '% Insured (Estimated)'
  AND att.frequency = 'Quarterly'
ORDER BY pct_uninsured DESC;

Track house prices and gross income inflow in a specific city

Compare Phoenix house prices vs gross income inflow.

WITH county_map AS (
    SELECT
        geo_id,
        geo_name,
        related_geo_id,
        related_geo_name
    FROM cybersyn.geography_relationships
    WHERE geo_name = 'Phoenix-Mesa-Scottsdale, AZ Metro Area'
    AND related_level = 'County'
), gross_income_data AS (
    SELECT
        geo_id,
        geo_name AS msa,
        date,
        SUM(value) AS gross_income_inflow
    FROM cybersyn.irs_origin_destination_migration_timeseries AS ts
    JOIN county_map ON (county_map.related_geo_id = ts.to_geo_id)
    WHERE ts.variable_name = 'Adjusted Gross Income'
    GROUP BY geo_id, msa, date
), home_price_data AS (
    SELECT LAST_DAY(date, 'year') AS end_date, AVG(value) AS home_price_index
    FROM cybersyn.fhfa_house_price_timeseries AS ts
    JOIN cybersyn.fhfa_house_price_attributes AS att
        ON (ts.variable = att.variable)
    WHERE geo_id IN (SELECT geo_id FROM county_map)
      AND att.index_type = 'purchase-only'
      AND att.seasonally_adjusted = TRUE
    GROUP BY end_date
)
SELECT
    msa,
    gix xxxxxxxome_price_data AS hpi ON (gid.date = hpi.end_date)
ORDER BY date;