Created
September 3, 2025 20:35
-
-
Save huksley/a8df5f0a041d3d5613705c557823e61d to your computer and use it in GitHub Desktop.
BTC price over time
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- Query to fetch Bitcoin price data and format as a nice table | |
| WITH api_response AS ( | |
| SELECT content::jsonb as data | |
| FROM http_get('https://api.coingecko.com/api/v3/coins/bitcoin/market_chart?vs_currency=usd&days=300&interval=daily') | |
| ), | |
| price_data AS ( | |
| SELECT | |
| jsonb_array_elements(data->'prices') as price_array | |
| FROM api_response | |
| ) | |
| SELECT | |
| -- Convert timestamp from milliseconds to readable date | |
| to_timestamp((price_array->>0)::bigint / 1000) AT TIME ZONE 'UTC' as date, | |
| to_char(to_timestamp((price_array->>0)::bigint / 1000) AT TIME ZONE 'UTC', 'Day, Mon DD, YYYY') as formatted_date, | |
| -- Format price with currency symbol and commas | |
| '$' || to_char((price_array->>1)::numeric, 'FM999,999,999.00') as price_usd, | |
| -- Raw price as numeric for calculations | |
| (price_array->>1)::numeric as price_numeric, | |
| -- Calculate day-over-day change (requires window function) | |
| CASE | |
| WHEN LAG((price_array->>1)::numeric) OVER (ORDER BY (price_array->>0)::bigint) IS NOT NULL | |
| THEN '$' || to_char( | |
| (price_array->>1)::numeric - LAG((price_array->>1)::numeric) OVER (ORDER BY (price_array->>0)::bigint), | |
| 'FM999,999,999.00' | |
| ) | |
| ELSE NULL | |
| END as daily_change, | |
| -- Calculate percentage change | |
| CASE | |
| WHEN LAG((price_array->>1)::numeric) OVER (ORDER BY (price_array->>0)::bigint) IS NOT NULL | |
| THEN ROUND( | |
| ((price_array->>1)::numeric - LAG((price_array->>1)::numeric) OVER (ORDER BY (price_array->>0)::bigint)) | |
| / LAG((price_array->>1)::numeric) OVER (ORDER BY (price_array->>0)::bigint) * 100, | |
| 2 | |
| ) || '%' | |
| ELSE NULL | |
| END as daily_change_pct | |
| FROM price_data | |
| ORDER BY (price_array->>0)::bigint; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment