<jupyter_start><jupyter_text>Find the division/district/year/month wise total_sale_price joining fact table and respective dimension table<jupyter_code># CUBE
cur = con.cursor()
q1 = "SELECT s.division, s.district, tim.year, tim.month, COUNT(t.total_price) " \
                "FROM star_schema.fact_table t " \
                "JOIN star_schema.store_dim s on s.store_key=t.store_key " \
                "JOIN star_schema.time_dim tim on tim.time_key=t.time_key " \
                "GROUP BY CUBE(s.division, s.district, tim.year, tim.month) " \
                "ORDER BY s.division"
cur.execute(q1)
record_n = cur.fetchall()
record_n
df_q1 = pd.DataFrame(list(record_n), columns=['divison', 'district', 'year', 'month','total_sales_price'])
df_q1
# ROLLUP
cur = con.cursor()
q1 = "SELECT s.division, s.district, tim.year, tim.month, COUNT(t.total_price) " \
                "FROM star_schema.fact_table t " \
                "JOIN star_schema.store_dim s on s.store_key=t.store_key " \
                "JOIN star_schema.time_dim tim on tim.time_key=t.time_key " \
                "GROUP BY ROLLUP(s.division, s.district, tim.year, tim.month) " \
                "ORDER BY s.division"
cur.execute(q1)
record_n = cur.fetchall()
df_q1 = pd.DataFrame(list(record_n), columns=['divison', 'district', 'year', 'month','total_sales_price'])
df_q1
class PostgresConnection(object):
    def __init__(self):
        self.connection = psycopg2.connect(database="ecomdb",
                                           user = "postgres",
                                           password = "<PASSWORD>",
                                           host = "127.0.0.1",
                                           port = "5432")

    def getConnection(self):
        print("Connection to DB established!")
        return self.connection
con = PostgresConnection().getConnection()
cur = con.cursor()
q1 = "SELECT  s.division, SUM(ft.total_price) " \
                    "FROM star_schema.fact_table ft " \
                    "JOIN star_schema.store_dim s ON ft.store_key=s.store_key " \
                    "GROUP BY s.division " \
                    "ORDER BY s.division"
cur.execute(q1)
record_n = cur.fetchall()

div = pd.DataFrame(list(record_n), columns=['division', 'sales'])
div
#Change type function
def changeType2Float(input):
    input['sales'] = input['sales'].astype('float64')
changeType2Float(div)
div_res=div.set_index(['division'])
div_res.plot.pie(y='sales',figsize=(5,5))<jupyter_output><empty_output><jupyter_text>DISTRICT<jupyter_code>cur = con.cursor()
q1_dis = "SELECT  s.district, SUM(ft.total_price) " \
                    "FROM star_schema.fact_table ft " \
                    "JOIN star_schema.store_dim s ON ft.store_key=s.store_key " \
                    "GROUP BY s.district " \
                    "ORDER BY s.district"
cur.execute(q1_dis)
record_n = cur.fetchall()

dis_res = pd.DataFrame(list(record_n), columns=['district', 'sales'])
dis_res
changeType2Float(dis_res)
dis_res=dis_res.set_index(['district'])
dis_res
import matplotlib as plt
dis_res.plot.pie(y='sales',figsize=(25,25))<jupyter_output><empty_output><jupyter_text>YEAR<jupyter_code>cur = con.cursor()
q1_year = "SELECT  tim.year, SUM(ft.total_price) " \
                    "FROM star_schema.fact_table ft " \
                    "JOIN star_schema.time_dim tim ON ft.time_key=tim.time_key " \
                    "GROUP BY tim.year " \
                    "ORDER BY tim.year"
cur.execute(q1_year)
record_n = cur.fetchall()

year_res = pd.DataFrame(list(record_n), columns=['year', 'sales'])
year_res
changeType2Float(year_res)
year_res=year_res.set_index(['year'])
year_res
year_res.plot.pie(y='sales',figsize=(10,10), shadow='True')<jupyter_output><empty_output><jupyter_text>MONTH<jupyter_code>cur = con.cursor()
q1_month = "SELECT  tim.month, SUM(ft.total_price) " \
                    "FROM star_schema.fact_table ft " \
                    "JOIN star_schema.time_dim tim ON ft.time_key=tim.time_key " \
                    "GROUP BY tim.month " \
                    "ORDER BY tim.month"
cur.execute(q1_month)
record_n = cur.fetchall()

month_res = pd.DataFrame(list(record_n), columns=['month', 'sales'])
month_res
changeType2Float(month_res)
month_res=month_res.set_index(['month'])
month_res
month_res.plot.pie(y='sales',figsize=(10,10))<jupyter_output><empty_output><jupyter_text>Q2: Find the customer/bank/transaction(cash/online) wise total_sale_price joining fact table and respective dimension table  CUSTOMER<jupyter_code>cur = con.cursor()
q2_customer = "SELECT  cus.name, SUM(ft.total_price) " \
                    "FROM star_schema.fact_table ft " \
                    "JOIN star_schema.customer_dim cus ON ft.customer_key=cus.customer_key " \
                    "GROUP BY cus.name " \
                    "ORDER BY cus.name"
cur.execute(q2_customer)
record_n = cur.fetchall()

customer_res = pd.DataFrame(list(record_n), columns=['name', 'sales'])
customer_res
changeType2Float(customer_res)
customer_res=customer_res.set_index(['name'])
customer_res
customer_res.plot.pie(y='sales',figsize=(10,10))
customer_res = customer_res[:10]
customer_res
customer_res.plot.pie(y='sales',figsize=(10,10))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1303766010323074, 'fraction_numerical': 0.011852418275664309, 'mean_word_length': 2.8899628252788103, 'pattern_counts': {'":': 0, '<': 22, '<?xml version=': 0, '>': 22, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 8, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '582738', 'license': 'Apache-2.0', 'n_tokens_mistral': 1828, 'n_tokens_neox': 1726, 'n_words': 305, 'path': 'notebook/analytics/olap.ipynb', 'repo_name': 'kazishuvo22/Health-Data-Analytics', 'size': '5226'}