# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

import pandas as pd
import pandas.io.sql as sqlio
import psycopg2
import matplotlib.pyplot as plt
import seaborn as sns
sns.set(font_scale=1.5, style="whitegrid")
from matplotlib.ticker import (MultipleLocator, FormatStrFormatter,AutoMinorLocator)
from matplotlib.dates import DateFormatter
import matplotlib.dates as mdates
import numpy as np
from connector import getConnection

# ### Interest income distribution

# +
conn = getConnection()

sql = """WITH interest_income_query AS(SELECT  i.sale_date_key, SUM(i.interest) AS interest_income
FROM installment i
GROUP BY i.sale_date_key) 
SELECT iq_2018.month, ROUND(iq_2018.ii::numeric,2) as "interest income 2018",
ROUND(iq_2019.ii::numeric,2) as "interest income 2019"
FROM  
(SELECT d.month, SUM(q.interest_income) AS ii 
FROM public."date" d, interest_income_query q
WHERE d.date_key = q.sale_date_key AND d.year = 2018 
group by d.month) AS iq_2018, 
(SELECT d.month, SUM(q.interest_income) AS ii 
FROM public."date" d, interest_income_query q
WHERE d.date_key = q.sale_date_key AND d.year = 2019 
GROUP BY d.month) AS iq_2019 
WHERE iq_2018.month = iq_2019.month 
ORDER BY iq_2019.month """

df = sqlio.read_sql_query(sql, conn)
df.head()
conn.close()
df.plot(x="month", y=["interest income 2018", "interest income 2019"], kind="bar",figsize=(15,10), rot = 0, colormap= 'Dark2_r')


# +
conn = getConnection()

sql = """WITH interest_income_query AS(SELECT  i.sale_date_key, SUM(i.interest) AS interest_income
FROM installment i
GROUP BY i.sale_date_key) 
SELECT 'interest' as income ,ROUND(iq_2018.ii::numeric,2) as "2018",
ROUND(iq_2019.ii::numeric,2) as "2019"
FROM  
(SELECT d.year, SUM(q.interest_income) AS ii 
FROM public."date" d, interest_income_query q
WHERE d.date_key = q.sale_date_key AND d.year = 2018 
group by d.year) AS iq_2018, 
(SELECT d.year, SUM(q.interest_income) AS ii 
FROM public."date" d, interest_income_query q
WHERE d.date_key = q.sale_date_key AND d.year = 2019 
GROUP BY d.year) AS iq_2019 
 """

df = sqlio.read_sql_query(sql, conn)
df = df.set_index('income')
conn.close()
df.head()
df.plot(y=["2018", "2019"], kind="bar",figsize=(15,10), rot = 0, colormap='Dark2_r')

# -

# ### Best selling month

# +
conn = getConnection()

sql = """SELECT d.month, SUM(s.loan_amount) as granted ,  
       RANK() OVER ( ORDER BY SUM(s.loan_amount) DESC ) best 
FROM sale s, public."date" d   
WHERE s.sale_date_key = d.date_key AND d.year = 2018 
GROUP BY  d.month; """

df = sqlio.read_sql_query(sql, conn)
df = df.set_index('month')
conn.close()
df.head()

df.plot.bar(y=['granted'],figsize=(15,10), rot = 0)
# -

# ### Best 7 days period for sales

# +
 
conn = getConnection()

sql = """SELECT iq.day, total_granted, week_total_sale_avg 
FROM  
(SELECT q1.date AS date, q1.month ,q1.day ,  
SUM(q1.loan_amount) AS total_granted,AVG(SUM(q1.loan_amount)) 
OVER ( ORDER BY q1.date_key ROWS BETWEEN 3 PRECEDING and 3 FOLLOWING ) AS week_total_sale_avg 
FROM  
(   
SELECT d.*, COALESCE(s.loan_amount ,0) AS loan_amount 
FROM 
public."date" d LEFT OUTER JOIN sale s 
ON d.date_key = s.sale_date_key 
WHERE d.year = 2018 AND 
    d.month in (2,3,4) 
)AS q1  
GROUP BY q1.date_key,q1.date, q1.month , q1.day
) AS iq 
WHERE iq.month = 3 """

df = sqlio.read_sql_query(sql, conn)
df = df.set_index('day')
df = df['week_total_sale_avg']
conn.close()
df.head()

df.plot.line(y=['week_total_sale_avg'],figsize=(15,10), rot = 0)



# +
 
conn = getConnection()

sql = """with unpaid_query as( 
    select  d.year, d.month, sum(amount) unpaid  
    from installment  i 
    left join payment p on i.installment_key = p.installment_key 
    join customer c on sale_customer_key = c.customer_key 
    join npl n on i.sale_customer_key = n.sale_customer_key 
    join "date" d on i.due_date_key  = d.date_key 
    where payment_date_key is null  
    and i.due_date_key < 731 -- 31-12-2019 
    group by  d.year, d.month)  
SELECT up_2019.month, round(up_2018.unpaid,2) as  "unpaid 2018", 
                      round(up_2019.unpaid,2) as "unpaid 2019" 
FROM   
   (SELECT q.month, unpaid  
    FROM unpaid_query q 
    WHERE q.year = 2019) up_2019 
left join  
   (SELECT q.month, unpaid  
    FROM unpaid_query q 
    WHERE q.year = 2018) up_2018 
    ON up_2019.month = up_2018.month 
ORDER BY up_2019.month   """

df = sqlio.read_sql_query(sql, conn)
df = df.set_index('month')
conn.close()
df.head()
#df.plot(y=["2018", "2019"], kind="bar",figsize=(15,10), rot = 0)

df.plot.bar(figsize=(15,10), rot = 0,colormap='Dark2')


# -

# ### Monthly status

# +
 
conn = getConnection()

sql = """
WITH  states AS (select month_end_Date,  count(*) as num,
(case when npl.min_date_npl is null then 'BONIS'
 when (month_end.date_key -npl.min_date_npl) > 365 then 'DEFAULT'
 when (month_end.date_key -npl.min_date_npl) > 180 then 'UTP' 
  when (month_end.date_key -npl.min_date_npl) > 90 then 'PASTDUE' else 'BONIS' end) status
from sale s left outer join npl on   s.customer_key = npl.sale_customer_key,
(select max(date_key) as date_key, max(date) as month_end_Date 
from "date"
group by year, MONTH 
HAVING max(date) < current_date
ORDER BY YEAR, MONTH ) month_end 
WHERE s.sale_date_key < month_end.date_key
GROUP BY month_end_date, status
ORDER BY month_end_date, status )
SELECT s_all.month_end_date, COALESCE(s_BONIS.num,0) AS BONIS, COALESCE(s_PASTDUE.num,0) AS PASTDUE ,
COALESCE(s_UTP.num,0) as UTP, COALESCE(s_DEFAULT.num,0) as DEFAULT
FROM states s_all 
LEFT OUTER JOIN states s_BONIS ON s_all.month_end_Date = s_BONIS.month_end_Date AND s_BONIS.status = 'BONIS'
LEFT OUTER JOIN states s_PASTDUE ON s_all.month_end_Date = s_PASTDUE.month_end_Date  AND s_PASTDUE.status = 'PASTDUE'
LEFT OUTER JOIN states s_UTP ON s_all.month_end_Date = s_UTP.month_end_Date  AND s_UTP.status = 'UTP'
LEFT OUTER JOIN states s_DEFAULT ON s_all.month_end_Date = s_DEFAULT.month_end_Date  AND s_DEFAULT.status = 'DEFAULT'
"""


df = sqlio.read_sql_query(sql, conn)
df = df.set_index('month_end_date')

color=(0.2, 0.4, 0.6, 0.6)
normalizedDataFrame = df.divide(df.sum(axis=1), axis=0)

conn.close()
normalizedDataFrame.head()
#df.plot(y=["2018", "2019"], kind="bar",figsize=(15,10), rot = 0)

#df.plot.bar(figsize=(15,10), rot = 0)
normalizedDataFrame.plot.area(stacked=True,figsize=(15,10),ylim = [0.9,1], colormap='cividis');

# -



StarcoderdataJupyterScriptsDedupFiltered