Location: DA/monetdblite-experiments/hmda-queries.sql

Hannes Muehleisen
Import
SELECT actiontype,
propertytype,
loanpurpose,
count(*) AS num_records
FROM hmda_11
GROUP BY actiontype,
propertytype,
loanpurpose;
SELECT actiontype,
propertytype,
loanpurpose,
count(*) AS num_records
FROM hmda_06
GROUP BY actiontype,
propertytype,
loanpurpose
ORDER BY actiontype,
propertytype,
loanpurpose;
SELECT actiontype,
propertytype,
loanpurpose,
count(*) AS num_records
FROM hmda_07
GROUP BY actiontype,
propertytype,
loanpurpose
ORDER BY actiontype,
propertytype,
loanpurpose;
SELECT actiontype,
propertytype,
loanpurpose,
count(*) AS num_records
FROM hmda_08
GROUP BY actiontype,
propertytype,
loanpurpose
ORDER BY actiontype,
propertytype,
loanpurpose;
SELECT actiontype,
propertytype,
loanpurpose,
count(*) AS num_records
FROM hmda_09
GROUP BY actiontype,
propertytype,
loanpurpose
ORDER BY actiontype,
propertytype,
loanpurpose;
SELECT actiontype,
propertytype,
loanpurpose,
count(*) AS num_records
FROM hmda_10
GROUP BY actiontype,
propertytype,
loanpurpose
ORDER BY actiontype,
propertytype,
loanpurpose;
SELECT actiontype,
propertytype,
loanpurpose,
count(*) AS num_records
FROM hmda_11
GROUP BY actiontype,
propertytype,
loanpurpose
ORDER BY actiontype,
propertytype,
loanpurpose;
SELECT sum((loanpurpose = '1'
AND propertytype IN ('1', '2'))) AS home_purchase,
sum((loanpurpose = '3'
AND propertytype IN ('1', '2'))) AS refinance,
sum((loanpurpose = '2'
AND propertytype IN ('1', '2'))) AS home_improvement,
sum((propertytype = '3')) AS multifamily,
count(*) AS total
FROM hmda_11
WHERE actiontype = '1';
SELECT sum((loanpurpose = '1'
AND propertytype IN ('1', '2'))) AS home_purchase,
sum((loanpurpose = '3'
AND propertytype IN ('1', '2'))) AS refinance,
sum((loanpurpose = '2'
AND propertytype IN ('1', '2'))) AS home_improvement,
sum((propertytype = '3')) AS multifamily,
count(*) AS total
FROM hmda_06
WHERE actiontype = '1'
UNION
SELECT sum((loanpurpose = '1'
AND propertytype IN ('1', '2'))) AS home_purchase,
sum((loanpurpose = '3'
AND propertytype IN ('1', '2'))) AS refinance,
sum((loanpurpose = '2'
AND propertytype IN ('1', '2'))) AS home_improvement,
sum((propertytype = '3')) AS multifamily,
count(*) AS total
FROM hmda_07
WHERE actiontype = '1'
UNION
SELECT sum((loanpurpose = '1'
AND propertytype IN ('1', '2'))) AS home_purchase,
sum((loanpurpose = '3'
AND propertytype IN ('1', '2'))) AS refinance,
sum((loanpurpose = '2'
AND propertytype IN ('1', '2'))) AS home_improvement,
sum((propertytype = '3')) AS multifamily,
count(*) AS total
FROM hmda_08
WHERE actiontype = '1'
UNION
SELECT sum((loanpurpose = '1'
AND propertytype IN ('1', '2'))) AS home_purchase,
sum((loanpurpose = '3'
AND propertytype IN ('1', '2'))) AS refinance,
sum((loanpurpose = '2'
AND propertytype IN ('1', '2'))) AS home_improvement,
sum((propertytype = '3')) AS multifamily,
count(*) AS total
FROM hmda_09
WHERE actiontype = '1'
UNION
SELECT sum((loanpurpose = '1'
AND propertytype IN ('1', '2'))) AS home_purchase,
sum((loanpurpose = '3'
AND propertytype IN ('1', '2'))) AS refinance,
sum((loanpurpose = '2'
AND propertytype IN ('1', '2'))) AS home_improvement,
sum((propertytype = '3')) AS multifamily,
count(*) AS total
FROM hmda_10
WHERE actiontype = '1'
UNION
SELECT sum((loanpurpose = '1'
AND propertytype IN ('1', '2'))) AS home_purchase,
sum((loanpurpose = '3'
AND propertytype IN ('1', '2'))) AS refinance,
sum((loanpurpose = '2'
AND propertytype IN ('1', '2'))) AS home_improvement,
sum((propertytype = '3')) AS multifamily,
count(*) AS total
FROM hmda_11
WHERE actiontype = '1';
SELECT race,
count(*)
FROM hmda_11
WHERE actiontype = '1'
AND loanpurpose = '1'
AND occupancy = '1'
AND lienstatus = '1'
AND propertytype IN ('1',
'2')
GROUP BY race
ORDER BY race;
SELECT ethnicity,
count(*)
FROM hmda_11
WHERE actiontype = '1'
AND loanpurpose = '1'
AND occupancy = '1'
AND lienstatus = '1'
AND propertytype IN ('1',
'2')
AND race = '5'
GROUP BY ethnicity
ORDER BY ethnicity;