TL;DR
For a market cap screen, choose the newest sufficiently covered session rather than using the raw latest date, which may contain only a partially published universe. This example accepts a session with at least 90% of the trailing median ticker count, or no prior median. That is a coverage heuristic, not proof of completeness. Inspect the selected date and count, then read the query's actual returned data; these are end of day values, not live quotes.
- Query name
- lake.stock_market_cap
- Storage partition
- year
- Columns
- 3
Columns and types
VARCHAR
- ticker
TIMESTAMP
- date
DOUBLE
- market_cap
Current logical schema for lake.stock_market_cap
Inspect the complete lake.stock_market_cap schema
| Column | Type |
|---|---|
| ticker | VARCHAR |
| date | TIMESTAMP |
| market_cap | DOUBLE |
No matching rows. Clear the filter to see all records.
Current logical schema for lake.stock_market_cap
Query lake.stock_market_cap using the column names and types below. The query validator and engine use this logical catalog; a field absent from an older shard returns typed NULL.
What this table contains
Logical projection of ticker/date/marketCap from SEC daily metrics.
Screen the newest sufficiently covered session
Query example, not executed here. The dates and filters define the proposed research scope; returned rows and current coverage must be checked in your workspace.
The documented coverage rule admits a session with at least 90% of its trailing median ticker count, or with no prior median. Inspect the chosen date and coverage count; the rule is a coverage heuristic, not a claim of perfect completeness. This is end-of-day data, not a live quote.
WITH sessions AS (
SELECT CAST(date AS DATE) AS session, COUNT(DISTINCT ticker) AS tickers
FROM lake.stock_market_cap
WHERE CAST(date AS DATE) >=
(SELECT MAX(CAST(date AS DATE)) FROM lake.stock_market_cap) - INTERVAL 45 DAY
GROUP BY 1
), scored AS (
SELECT session, tickers,
MEDIAN(tickers) OVER (ORDER BY session ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING) AS trailing_median
FROM sessions
), latest_full AS (
SELECT MAX(session) AS session
FROM scored
WHERE trailing_median IS NULL OR tickers >= 0.9 * trailing_median
)
SELECT ticker, date, market_cap
FROM lake.stock_market_cap
WHERE CAST(date AS DATE) = (SELECT session FROM latest_full)
AND market_cap IS NOT NULL
ORDER BY market_cap DESC, ticker
LIMIT 20;Submit the stock_market_cap example and read back its parts
Run the exact SQL through the Python SDK
Save this page's SQL as query.sql and the runner below as query.py. Use a registered key with lake scope from Developers. The runner submits the displayed stock_market_cap query once, saves its operation, waits on that same ID, saves the returned manifest and downloads its Parquet parts.
This submission has not been executed for the reference page. Inspect the SQL, dates and resource limits before running it. Reuse the request key only for this identical SQL and limits; use a new key when changing them.
python3 -m pip install nexustrade==1.42.0
export NEXUSTRADE_API_KEY="YOUR_LAKE_SCOPED_KEY"
python3 query.py
import json
from pathlib import Path
from nexustrade import NexusTradeClient
# Save the SQL displayed on this page as query.sql.
sql = Path("query.sql").read_text()
client = NexusTradeClient.from_environment()
operation = client.create_lake_query(
{"query": sql, "limits": {"timeoutSeconds": 30, "maxRows": 50,
"maxResultBytes": 1048576}},
idempotency_key="reference-stock_market_cap-20261012-v1",
)
Path("query-operation.json").write_text(json.dumps(operation, indent=2))
finished = client.wait_for_lake_query(operation["id"])
if finished["status"] != "completed":
raise RuntimeError(finished)
manifest = client.get_lake_query_manifest(operation["id"])["result"]
Path("query-manifest.json").write_text(json.dumps(manifest, indent=2))
print(json.dumps(manifest, indent=2))
for part in manifest["parts"]:
Path(f"query-{part['part']}.parquet").write_bytes(
client.download_lake_query_part(operation["id"], part["part"], max_bytes=1048576)
)
Coverage and timing
Inspect source coverage and timing before using the rows
Logical projection of ticker/date/marketCap from SEC daily metrics.
Latest screens SHOULD pin to the newest session with full coverage, not raw `MAX(date)`: a partially published session can sit at the tip with a fraction of the universe. Use `WHERE date::DATE = (SELECT session FROM latest_full)` with `WITH latest_full AS (WITH sessions AS ( SELECT date::DATE AS session, COUNT(DISTINCT ticker) AS tickers FROM lake.stock_market_cap WHERE date::DATE >= (SELECT MAX(date::DATE) FROM lake.stock_market_cap) - INTERVAL 45 DAY GROUP BY 1 ), scored AS ( SELECT session, tickers, MEDIAN(tickers) OVER (ORDER BY session ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING) AS trailing_median FROM sessions ) SELECT MAX(session) AS session FROM scored WHERE trailing_median IS NULL OR tickers >= 0.9 * trailing_median)`.
No comments yet.