Notes

WITH ranked_data AS (
SELECT
qu.as_of_date,
cl.qra_country,
cm.marketcap_usd_qra,
-- Rank companies by market cap within each 'as_of_date'
RANK() OVER (PARTITION BY qu.as_of_date ORDER BY cm.marketcap_usd_qra DESC) AS rank,
-- Calculate total number of companies within each 'as_of_date'
COUNT(*) OVER (PARTITION BY qu.as_of_date) AS total_companies
FROM
gra_scratch.qra_universe qu
-- Add company location
LEFT JOIN
gra_scratch.company_locations cl
ON
cl.as_of_date = qu.as_of_date
AND cl.xf_company_id = qu.xf_company_id
AND cl.gvkey = qu.gvkey
-- Add market cap
INNER JOIN
gra_scratch.company_marketcaps cm
ON
qu.as_of_date = cm.as_of_date
AND qu.xf_company_id = cm.xf_company_id
WHERE
cl.qra_country = 'US'
AND qu.as_of_date = '2024-05-31'
)
-- Filter for top 70% of companies based on market cap
SELECT
as_of_date,
qra_country,
marketcap_usd_qra
FROM
ranked_data
WHERE
(rank * 1.0 / total_companies) <= 0.7
ORDER BY
as_of_date, marketcap_usd_qra DESC;