Skip to main content

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);

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;