def card_stats(min_age=0, region_name=None):
# From q2
description = "This query returns the number of cards issued."
# subtitle = "Api data filters: min_age, region_name"
where_region = ''
if region_name is not None:
# add extra restriction
where_region = f" AND region_name = '{region_name}'"
# TODO: select AVG(INHABITANTS) AVG_IHABITANTS
sql = f"""
SELECT
TO_CHAR(issued, 'YYYY-MM') month
, COUNT(*) num_rows
FROM
dmelisso.CARD cr
JOIN dmelisso.DISPOSITION d ON d.disp_id = cr.disp_id
JOIN dmelisso.CLIENT c ON c.client_id = d.client_id
JOIN dmelisso.DISTRICT dist ON dist.DISTRICT_ID = c.DISTRICT_ID
LEFT JOIN dmelisso.REGION_FULL r ON r.REGION_ID = dist.REGION_ID
WHERE
1999 - EXTRACT(YEAR FROM BIRTH_NUMBER) > {min_age}
{where_region}
GROUP BY
TO_CHAR(issued, 'YYYY-MM')
ORDER BY
1
"""
return fetch_data(sql, description)
def loan_stats():
# From q3
description = """
This query returns the average loan monthy payment, and the number of loans issued
each month for different age groups"""
# Note: includes even the months without data
sql = """
SELECT
X.AGE_GROUP, X.MONTH, Y.AVG_PAYMENTS, Y.NUM_LOANS FROM (
SELECT *
FROM (SELECT 'g60' AGE_GROUP FROM DUAL
UNION ALL SELECT 'g30-60' FROM DUAL
UNION ALL SELECT 'g0-30' FROM DUAL
) GRP
CROSS JOIN
(
WITH t as (
select date '1993-07-05' init, date '1998-12-08' final
from dual
)
select to_char(add_months(trunc(init, 'mm'), level - 1), 'RRRR-MM') MONTH
from t
connect by level <= months_between(final, init) + 1
)
ORDER BY GRP.AGE_GROUP, MONTH
) X
LEFT JOIN
(
-- average payments for age groups
SELECT CASE
WHEN 1999 - EXTRACT(YEAR FROM BIRTH_NUMBER) >= 60 THEN 'g60'
WHEN 1999 - EXTRACT(YEAR FROM BIRTH_NUMBER) >= 30 THEN 'g30-60'
ELSE 'g0-30'
END AS AGE_GROUP
, TO_CHAR(START_DATE, 'YYYY-MM') AS LOAN_CREATED_MONTH
, AVG(PAYMENTS) AS AVG_PAYMENTS
, COUNT(*) AS NUM_LOANS
-- , AVG(AMOUNT) AS AVG_AMOUNT
-- , AVG(DURATION) AS AVG_DURATION
FROM dmelisso.LOAN L
JOIN dmelisso.DISPOSITION D ON D.account_id = L.account_id
JOIN dmelisso.CLIENT c ON c.client_id = d.client_id
WHERE
-- can filter by age at the time of data collection
1999 - EXTRACT(YEAR FROM BIRTH_NUMBER) > 10
AND D.TYPE = 'OWNER'
GROUP BY CASE
WHEN 1999 - EXTRACT(YEAR FROM BIRTH_NUMBER) >= 60 THEN 'g60'
WHEN 1999 - EXTRACT(YEAR FROM BIRTH_NUMBER) >= 30 THEN 'g30-60'
ELSE 'g0-30'
END
, TO_CHAR(START_DATE, 'YYYY-MM')
ORDER BY 1, 2
) Y ON Y.AGE_GROUP = X.AGE_GROUP AND Y.LOAN_CREATED_MONTH = X.MONTH
ORDER BY X.AGE_GROUP, X.MONTH
"""
return fetch_data(sql, description)
def transaction_balance1():
# From q1
description = "Group the loan by district and calculate the total amount of loan on each date in each district"
sql = """
SELECT SUM(LOAN.amount) As LOAN_SUM, TO_CHAR(loan.start_DATE, 'YYYY-MM') AS month, DISTRICT_detail.district_id
FROM dmelisso.LOAN LOAN
JOIN dmelisso.account ACCOUNT
ON ACCOUNT.account_id = LOAN.account_id
JOIN dmelisso.DISTRICT_detail
ON DISTRICT_detail.district_id = ACCOUNT.district_id
GROUP BY TO_CHAR(loan.start_DATE, 'YYYY-MM'), DISTRICT_detail.district_id
ORDER BY TO_CHAR(loan.start_DATE, 'YYYY-MM')
"""
json_data = fetch_data(sql, description)
return json_data
def transaction_balance2():
# From q1
description = "Calculate the amount of cards issued on each month"
sql = """
SELECT COUNT(CARD.card_id) AS Number_Of_Cards, TO_CHAR(CARD.issued, 'YYYY-MM') AS month
FROM dmelisso.CARD
JOIN dmelisso.DISPOSITION
ON DISPOSITION.disp_id = CARD.disp_id
JOIN dmelisso.ACCOUNT
ON ACCOUNT.account_id = DISPOSITION.account_id
GROUP BY TO_CHAR(CARD.issued, 'YYYY-MM')
ORDER BY TO_CHAR(CARD.issued, 'YYYY-MM')
"""
json_data = fetch_data(sql, description)
return json_data
def table_stats():
# q0 - total number of records in each table
description = "This query returns the number of rows in each table"
sql = """
SELECT 'ACCOUNT' table_name, COUNT(*) num_rows FROM dmelisso.ACCOUNT
UNION ALL SELECT 'ORDER', COUNT(*) FROM dmelisso.ORDERS
UNION ALL SELECT 'REGION_FULL', COUNT(*) FROM dmelisso.REGION_FULL
UNION ALL SELECT 'DISTRICT', COUNT(*) FROM dmelisso.DISTRICT
UNION ALL SELECT 'DISTRICT_DETAIL', COUNT(*) FROM dmelisso.DISTRICT_DETAIL
UNION ALL SELECT 'DISTRICT_HISTORY', COUNT(*) FROM dmelisso.DISTRICT_HISTORY
UNION ALL SELECT 'DISTRICT_POPULATION', COUNT(*) FROM dmelisso.DISTRICT_POPULATION
UNION ALL SELECT 'TRANSACTIONS', COUNT(*) FROM dmelisso.TRANSACTIONS
UNION ALL SELECT 'CLIENT', COUNT(*) FROM dmelisso.CLIENT
UNION ALL SELECT 'CARD', COUNT(*) FROM dmelisso.CARD
UNION ALL SELECT 'DISPOSITION', COUNT(*) FROM dmelisso.DISPOSITION
"""
return fetch_data(sql, description)
def transaction_balance():
# From q1
description = "This query returns the sum of transaction balance by gender"
sql = """
SELECT
TO_CHAR(t.created_DATE, 'YYYY-MM') AS month,
c.gender,
SUM(t.balance) AS sum_balance
FROM
dmelisso.client c
JOIN dmelisso.disposition d ON c.client_id = d.client_id
JOIN dmelisso.transactions t ON t.account_id = d.account_id
GROUP BY
TO_CHAR(created_DATE, 'YYYY-MM'),
gender
ORDER BY
TO_CHAR(created_DATE, 'YYYY-MM')
"""
json_data = fetch_data(sql, description)
return json_data
def num_accounts_for_dispositions_disp():
# From q4
description = "Number of accounts open for disposition types (owner/disponent) and region"
sql = """
SELECT
R.REGION_NAME
, count(*) AS num_accounts
, TO_CHAR(A.CREATED_DATE, 'YYYY-MM') month
FROM
dmelisso.DISPOSITION D
JOIN dmelisso.ACCOUNT A ON A.ACCOUNT_ID = A.ACCOUNT_ID
JOIN dmelisso.CLIENT C ON C.CLIENT_ID = A.ACCOUNT_ID
JOIN dmelisso.DISTRICT D ON D.ACCOUNT_ID = A.ACCOUNT_ID
JOIN dmelisso.REGION_FULL R ON R.REGION_ID = D.REGION_ID
WHERE
D.TYPE = 'DISPONENT'
GROUP BY
TO_CHAR(A.CREATED_DATE, 'YYYY-MM')
, R.REGION_NAME
ORDER BY
TO_CHAR(A.CREATED_DATE, 'YYYY-MM')
"""
json_data = fetch_data(sql, description)
return json_data