Sample Treasury.IO queries

This page contains useful queries of Treasury.io data, written in SQL format.

Any questions about the queries? Ask @Cezary.

Sample Query 1: Daily distance from the Federal Debt Ceiling

Run the following query to see how close we are to breaching the government's debt ceiling each day.

select 

a.date, 
a.close_today as Debt_Ceiling,
b.close_today as Debt_Subject_To_Ceiling,
a.close_today - b.close_today as Distance_From_Debt_Ceiling

from 

t3c a inner join t3c b
on a.date = b.date

where 

a.item = 'Statutory Debt Limit'
and
b.item = 'Total Public Debt Subject to Limit'

order by a.date desc

Here is a graph of the query output - debt vs. the debt ceiling - over time:

Here are the first ten rows of the query output (as of 7-11-2013):

Date	Limit	Subject	Distance
7/1/2013	16,699,421	16,699,396	25
6/28/2013	16,699,421	16,699,396	25
6/27/2013	16,699,421	16,699,396	25
6/26/2013	16,699,421	16,699,396	25
6/25/2013	16,699,421	16,699,396	25
6/24/2013	16,699,421	16,699,396	25
6/21/2013	16,699,421	16,699,396	25
6/20/2013	16,699,421	16,699,396	25
6/19/2013	16,699,421	16,699,396	25
6/18/2013	16,699,421	16,699,396	25

Sample Query 2: Federal spending on Food programs

Food Stamps are in the news: on July 11, House Republicans passed a farm bill without the Food Stamp program, marking the first time Food Stamps have not been part of the bill since 1973, according to the New York Times.

So how much do food stamps cost the Federal government? The Treasury aggregates payments for the Food Stamp program in two line items - "Food Stamps" and "Food and Nutrition Service ( misc )". Run the query below to get spending on Federal food programs, which will mostly (but not entirely) reflect the Food Stamp program.

For a comparison, see the Congressional Budget Office, which provides an overview of the Food Stamp program here. The amounts are smaller because Treasury doesn't cleanly delineate spending for just the Food Stamp program, but the overall trajectory is the same.

select 

year_month, 
sum(today) as Food_Programs_Spend 

from t2

where item IN ('Food Stamps','Food and Nutrition Service ( misc )')
and transaction_type = 'withdrawal'
and is_total = 0

group by year_month
order by year_month desc

The above query aggregates the Food spending on a month-by-month basis, but you can easily view it day-by-day by selecting the date field instead of the year_month identifier:

select 

date, 
sum(today) as Food_Programs_Spend 

from t2

where item IN ('Food Stamps','Food and Nutrition Service ( misc )')
and transaction_type = 'withdrawal'
and is_total = 0

group by date
order by date desc

Below is a graph of the monthly food program spending query output:

Below are the first ten output rows for the monthly query (as of 7-17-2013):

year_month	Food_Programs_Spend
2013-06	8,485
2013-05	9,537
2013-04	9,134
2013-03	9,259
2013-02	8,688
2013-01	9,041
2012-12	9,493
2012-11	7,745
2012-10	9,209
2012-09	7,660