Calculations › Market Breakdown
Show the average latest price by exchange across all symbols
The query
WITH latest AS (
SELECT DISTINCT ON (tp.ticketnameid) tp.ticketnameid, tp.price
FROM ticketprice tp
ORDER BY tp.ticketnameid, tp.datetime DESC
)
SELECT sm.exchange, AVG(l.price) AS avg_price, COUNT(*) AS n
FROM latest l
JOIN symbolmetadata sm ON sm.ticketnameid = l.ticketnameid
WHERE sm.exchange IS NOT NULL AND sm.exchange != ''
GROUP BY sm.exchange
ORDER BY n DESC
LIMIT 15;
Runs against Cryptobi's market tables — ticketname (symbols),
ticketprice (daily prices and volume) and symbolmetadata
(sector, exchange, market cap and other reference fields).
Current output
| Exchange | Avg price | Count |
|---|---|---|
| STO | 297.88 | 545 |
| CCY | 1.63 | 35 |
| NYQ | 224.57 | 34 |
| NMS | 275.15 | 32 |
| TOR | 116.52 | 30 |
| PCX | 251.93 | 23 |
| CCC | 3851.52 | 23 |
| OSL | 144.57 | 17 |
| FRA | 232.65 | 12 |
| GER | 180.52 | 9 |
| NYM | 532.70 | 6 |
| NGM | 180.23 | 5 |
| CPH | 801.83 | 4 |
| NCM | 9.08 | 4 |
| PNK | 37.65 | 4 |
Live output, first 15 rows, refreshed periodically. Model estimates are trend projections from recent prices, not financial advice.
Run this on your own holdings, change it, or write your own in SQL or Python — then put it on a dashboard or have it emailed on a schedule. Free, no credit card.
Sign Up Free