test.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "import streamlit as st\n",
    "import pandas as pd\n",
    "from constant import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Quarter</th>\n",
       "      <th>Year</th>\n",
       "      <th>ExtendedPrice</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>110.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>143.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>112.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>112.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>102.96</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Quarter    Year  ExtendedPrice\n",
       "0      1.0  2009.0         110.88\n",
       "1      1.0  2010.0         143.86\n",
       "2      1.0  2011.0         112.86\n",
       "3      1.0  2012.0         112.86\n",
       "4      1.0  2013.0         102.96"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "req_1 = pd.merge(df_FactSales, df_DimDate, left_on='InvoiceDateID', right_on='DateID')\n",
    "req_1 = req_1.groupby(['Quarter', 'Year']).agg({'ExtendedPrice': 'sum'}).reset_index()\n",
    "req_1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FullDate</th>\n",
       "      <th>Year</th>\n",
       "      <th>ExtendedPrice</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2009-01-01</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>1.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>3.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2009-01-03</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>5.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2009-01-06</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>8.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2009-01-11</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>13.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>349</th>\n",
       "      <td>2013-12-05</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>3.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>350</th>\n",
       "      <td>2013-12-06</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>5.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>351</th>\n",
       "      <td>2013-12-09</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>8.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>352</th>\n",
       "      <td>2013-12-14</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>13.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>353</th>\n",
       "      <td>2013-12-22</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>1.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>354 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       FullDate    Year  ExtendedPrice\n",
       "0    2009-01-01  2009.0           1.98\n",
       "1    2009-01-02  2009.0           3.96\n",
       "2    2009-01-03  2009.0           5.94\n",
       "3    2009-01-06  2009.0           8.91\n",
       "4    2009-01-11  2009.0          13.86\n",
       "..          ...     ...            ...\n",
       "349  2013-12-05  2013.0           3.96\n",
       "350  2013-12-06  2013.0           5.94\n",
       "351  2013-12-09  2013.0           8.91\n",
       "352  2013-12-14  2013.0          13.86\n",
       "353  2013-12-22  2013.0           1.99\n",
       "\n",
       "[354 rows x 3 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "req_1_day = pd.merge(df_FactSales, df_DimDate, left_on='InvoiceDateID', right_on='DateID')\n",
    "req_1_day = req_1_day.groupby(['FullDate', 'Year']).agg({'ExtendedPrice': 'sum'}).reset_index()\n",
    "req_1_day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "req_1_month = pd.merge(df_FactSales, df_DimDate, left_on='InvoiceDateID', right_on='DateID')\n",
    "req_1_month = req_1_month.groupby(['MonthOfYear', 'MonthName','Year']).agg({'ExtendedPrice': 'sum'}).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>Total Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2009.0</td>\n",
       "      <td>449.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2010.0</td>\n",
       "      <td>481.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011.0</td>\n",
       "      <td>469.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2012.0</td>\n",
       "      <td>477.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013.0</td>\n",
       "      <td>450.58</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Year  Total Sales\n",
       "0  2009.0       449.46\n",
       "1  2010.0       481.45\n",
       "2  2011.0       469.58\n",
       "3  2012.0       477.53\n",
       "4  2013.0       450.58"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "req_1_year = pd.merge(df_FactSales, df_DimDate, left_on='InvoiceDateID', right_on='DateID')\n",
    "req_1_year = req_1_year.groupby(['Year']).agg({'ExtendedPrice': 'sum'}).reset_index()\n",
    "req_1_year.rename(columns={'ExtendedPrice':'Total Sales'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AlbumId</th>\n",
       "      <th>AlbumTitle</th>\n",
       "      <th>Views</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>For Those About To Rock We Salute You</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Balls to the Wall</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Restless and Wild</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Let There Be Rock</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Big Ones</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   AlbumId                             AlbumTitle  Views\n",
       "0        1  For Those About To Rock We Salute You     21\n",
       "1        2                      Balls to the Wall      3\n",
       "2        3                      Restless and Wild     12\n",
       "3        4                      Let There Be Rock     16\n",
       "4        5                               Big Ones     45"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "req_2 = pd.merge(df_FactListen, df_DimAlbum, how='inner', on='AlbumId')\n",
    "req_2 = req_2.groupby(['AlbumId', 'AlbumTitle']).agg({'PlaylistTrackViews': 'sum'}).reset_index().rename(columns={'PlaylistTrackViews':'Views'})\n",
    "# req_2 = req_2.sort_values('Views', ascending=False, inplace=True)\n",
    "req_2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AlbumId</th>\n",
       "      <th>AlbumTitle</th>\n",
       "      <th>No of Track</th>\n",
       "      <th>Song Duration</th>\n",
       "      <th>Views</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>For Those About To Rock We Salute You</td>\n",
       "      <td>10</td>\n",
       "      <td>2400415</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Balls to the Wall</td>\n",
       "      <td>1</td>\n",
       "      <td>342562</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Restless and Wild</td>\n",
       "      <td>3</td>\n",
       "      <td>858088</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Let There Be Rock</td>\n",
       "      <td>8</td>\n",
       "      <td>2453259</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Big Ones</td>\n",
       "      <td>15</td>\n",
       "      <td>4411709</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>342</th>\n",
       "      <td>343</td>\n",
       "      <td>Respighi:Pines of Rome</td>\n",
       "      <td>1</td>\n",
       "      <td>286741</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343</th>\n",
       "      <td>344</td>\n",
       "      <td>Schubert: The Late String Quartets &amp; String Qu...</td>\n",
       "      <td>1</td>\n",
       "      <td>139200</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>344</th>\n",
       "      <td>345</td>\n",
       "      <td>Monteverdi: L'Orfeo</td>\n",
       "      <td>1</td>\n",
       "      <td>66639</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>345</th>\n",
       "      <td>346</td>\n",
       "      <td>Mozart: Chamber Music</td>\n",
       "      <td>1</td>\n",
       "      <td>221331</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>346</th>\n",
       "      <td>347</td>\n",
       "      <td>Koyaanisqatsi (Soundtrack from the Motion Pict...</td>\n",
       "      <td>1</td>\n",
       "      <td>206005</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>347 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     AlbumId                                         AlbumTitle  No of Track  \\\n",
       "0          1              For Those About To Rock We Salute You           10   \n",
       "1          2                                  Balls to the Wall            1   \n",
       "2          3                                  Restless and Wild            3   \n",
       "3          4                                  Let There Be Rock            8   \n",
       "4          5                                           Big Ones           15   \n",
       "..       ...                                                ...          ...   \n",
       "342      343                             Respighi:Pines of Rome            1   \n",
       "343      344  Schubert: The Late String Quartets & String Qu...            1   \n",
       "344      345                                Monteverdi: L'Orfeo            1   \n",
       "345      346                              Mozart: Chamber Music            1   \n",
       "346      347  Koyaanisqatsi (Soundtrack from the Motion Pict...            1   \n",
       "\n",
       "     Song Duration  Views  \n",
       "0          2400415     21  \n",
       "1           342562      3  \n",
       "2           858088     12  \n",
       "3          2453259     16  \n",
       "4          4411709     45  \n",
       "..             ...    ...  \n",
       "342         286741      5  \n",
       "343         139200      4  \n",
       "344          66639      4  \n",
       "345         221331      4  \n",
       "346         206005      5  \n",
       "\n",
       "[347 rows x 5 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "req_2_1 = pd.merge(df_FactListen, df_DimAlbum, how='inner', on='AlbumId')\n",
    "req_2_1 = pd.merge(req_2_1, df_DimGenre, how='inner', on='GenreId')\n",
    "req_2_1 = req_2_1.groupby(['AlbumId', 'AlbumTitle']).agg({\n",
    "    'TrackId': 'count',\n",
    "    'Milliseconds': 'sum',\n",
    "    'PlaylistTrackViews': 'sum'\n",
    "}).reset_index()\n",
    "\n",
    "# Rename the columns\n",
    "req_2_1.rename(columns={\n",
    "    'TrackId': 'No of Track',\n",
    "    'Milliseconds': 'Song Duration',\n",
    "    'PlaylistTrackViews': 'Views'\n",
    "}, inplace=True)\n",
    "req_2_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CustomerId</th>\n",
       "      <th>CustomerName</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Luís Gonçalves</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Luís Gonçalves</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Luís Gonçalves</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Luís Gonçalves</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>Leonie Köhler</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225</th>\n",
       "      <td>58</td>\n",
       "      <td>Manoj Pareek</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>229</th>\n",
       "      <td>59</td>\n",
       "      <td>Puja Srivastava</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>230</th>\n",
       "      <td>59</td>\n",
       "      <td>Puja Srivastava</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228</th>\n",
       "      <td>59</td>\n",
       "      <td>Puja Srivastava</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>231</th>\n",
       "      <td>59</td>\n",
       "      <td>Puja Srivastava</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>232 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     CustomerId     CustomerName    Year  Month\n",
       "0             1   Luís Gonçalves  2010.0      3\n",
       "1             1   Luís Gonçalves  2011.0      1\n",
       "2             1   Luís Gonçalves  2012.0      2\n",
       "3             1   Luís Gonçalves  2013.0      1\n",
       "4             2    Leonie Köhler  2009.0      3\n",
       "..          ...              ...     ...    ...\n",
       "225          58     Manoj Pareek  2011.0      1\n",
       "229          59  Puja Srivastava  2010.0      1\n",
       "230          59  Puja Srivastava  2011.0      2\n",
       "228          59  Puja Srivastava  2009.0      2\n",
       "231          59  Puja Srivastava  2012.0      1\n",
       "\n",
       "[232 rows x 4 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_temp = pd.merge(df_FactGenre, df_DimDate, how='inner', left_on='OrderDateID', right_on='DateID')\n",
    "df_temp = df_temp.groupby(['CustomerId', 'Year']).agg({'MonthOfYear': 'nunique'}).reset_index()\n",
    "df_temp.rename(columns={'MonthOfYear': 'Month'}, inplace=True)\n",
    "req_3 = pd.merge(df_temp, df_DimCustomer, how='inner', on='CustomerId')\n",
    "req_3 = req_3[['CustomerId', 'CustomerName', 'Year', 'Month']].sort_values('CustomerId')\n",
    "req_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_temp = pd.merge(df_FactGenre, df_DimDate, how='inner', left_on='OrderDateID', right_on='DateID')\n",
    "\n",
    "df_temp = df_temp.groupby('CustomerId').agg({'Year': 'nunique'}).reset_index()\n",
    "\n",
    "req_3_year = pd.merge(df_temp, df_DimCustomer, how='inner', on='CustomerId')\n",
    "req_3_year = req_3_year[['CustomerId', 'CustomerName', 'Year']].sort_values('CustomerId')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CustomerId</th>\n",
       "      <th>CustomerName</th>\n",
       "      <th>Total No of invoices</th>\n",
       "      <th>Total Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Helena Holý</td>\n",
       "      <td>38</td>\n",
       "      <td>49.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>26</td>\n",
       "      <td>Richard Cunningham</td>\n",
       "      <td>38</td>\n",
       "      <td>47.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>57</td>\n",
       "      <td>Luis Rojas</td>\n",
       "      <td>38</td>\n",
       "      <td>46.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>45</td>\n",
       "      <td>Ladislav Kovács</td>\n",
       "      <td>38</td>\n",
       "      <td>45.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>46</td>\n",
       "      <td>Hugh O'Reilly</td>\n",
       "      <td>38</td>\n",
       "      <td>45.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>37</td>\n",
       "      <td>Fynn Zimmermann</td>\n",
       "      <td>38</td>\n",
       "      <td>43.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>24</td>\n",
       "      <td>Frank Ralston</td>\n",
       "      <td>38</td>\n",
       "      <td>43.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>28</td>\n",
       "      <td>Julia Barnett</td>\n",
       "      <td>38</td>\n",
       "      <td>43.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>25</td>\n",
       "      <td>Victor Stevens</td>\n",
       "      <td>38</td>\n",
       "      <td>42.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>Astrid Gruber</td>\n",
       "      <td>38</td>\n",
       "      <td>42.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>44</td>\n",
       "      <td>Terhi Hämäläinen</td>\n",
       "      <td>38</td>\n",
       "      <td>41.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>43</td>\n",
       "      <td>Isabelle Mercier</td>\n",
       "      <td>38</td>\n",
       "      <td>40.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>František Wichterlová</td>\n",
       "      <td>38</td>\n",
       "      <td>40.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>48</td>\n",
       "      <td>Johannes Van der Berg</td>\n",
       "      <td>38</td>\n",
       "      <td>40.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20</td>\n",
       "      <td>Dan Miller</td>\n",
       "      <td>38</td>\n",
       "      <td>39.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>42</td>\n",
       "      <td>Wyatt Girard</td>\n",
       "      <td>38</td>\n",
       "      <td>39.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>34</td>\n",
       "      <td>João Fernandes</td>\n",
       "      <td>38</td>\n",
       "      <td>39.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>22</td>\n",
       "      <td>Heather Leacock</td>\n",
       "      <td>38</td>\n",
       "      <td>39.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Luís Gonçalves</td>\n",
       "      <td>38</td>\n",
       "      <td>39.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>17</td>\n",
       "      <td>Jack Smith</td>\n",
       "      <td>38</td>\n",
       "      <td>39.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>François Tremblay</td>\n",
       "      <td>38</td>\n",
       "      <td>39.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Bjørn Hansen</td>\n",
       "      <td>38</td>\n",
       "      <td>39.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>39</td>\n",
       "      <td>Camille Bernard</td>\n",
       "      <td>38</td>\n",
       "      <td>38.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>58</td>\n",
       "      <td>Manoj Pareek</td>\n",
       "      <td>38</td>\n",
       "      <td>38.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>40</td>\n",
       "      <td>Dominique Lefebvre</td>\n",
       "      <td>38</td>\n",
       "      <td>38.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>19</td>\n",
       "      <td>Tim Goyer</td>\n",
       "      <td>38</td>\n",
       "      <td>38.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>15</td>\n",
       "      <td>Jennifer Peterson</td>\n",
       "      <td>38</td>\n",
       "      <td>38.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>51</td>\n",
       "      <td>Joakim Johansson</td>\n",
       "      <td>38</td>\n",
       "      <td>38.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>47</td>\n",
       "      <td>Lucas Mancini</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>53</td>\n",
       "      <td>Phil Hughes</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>49</td>\n",
       "      <td>Stanislaw Wójcik</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>55</td>\n",
       "      <td>Mark Taylor</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>56</td>\n",
       "      <td>Diego Gutiérrez</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>52</td>\n",
       "      <td>Emma Jones</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>41</td>\n",
       "      <td>Marc Dubois</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>50</td>\n",
       "      <td>Enrique Muñoz</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>54</td>\n",
       "      <td>Steve Murray</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>30</td>\n",
       "      <td>Edward Francis</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>38</td>\n",
       "      <td>Niklas Schröder</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>18</td>\n",
       "      <td>Michelle Brooks</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>Daan Peeters</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>Kara Nielsen</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>Eduardo Martins</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>Alexandre Rocha</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>Roberto Almeida</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>Fernanda Ramos</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>Mark Philips</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16</td>\n",
       "      <td>Frank Harris</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>21</td>\n",
       "      <td>Kathy Chase</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>36</td>\n",
       "      <td>Hannah Schneider</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>23</td>\n",
       "      <td>John Gordon</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>27</td>\n",
       "      <td>Patrick Gray</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>29</td>\n",
       "      <td>Robert Brown</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Leonie Köhler</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>31</td>\n",
       "      <td>Martha Silk</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>32</td>\n",
       "      <td>Aaron Mitchell</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>33</td>\n",
       "      <td>Ellie Sullivan</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>35</td>\n",
       "      <td>Madalena Sampaio</td>\n",
       "      <td>38</td>\n",
       "      <td>37.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>59</td>\n",
       "      <td>Puja Srivastava</td>\n",
       "      <td>36</td>\n",
       "      <td>36.64</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    CustomerId           CustomerName  Total No of invoices  Total Sales\n",
       "5            6            Helena Holý                    38        49.62\n",
       "25          26     Richard Cunningham                    38        47.62\n",
       "56          57             Luis Rojas                    38        46.62\n",
       "44          45        Ladislav Kovács                    38        45.62\n",
       "45          46          Hugh O'Reilly                    38        45.62\n",
       "36          37        Fynn Zimmermann                    38        43.62\n",
       "23          24          Frank Ralston                    38        43.62\n",
       "27          28          Julia Barnett                    38        43.62\n",
       "24          25         Victor Stevens                    38        42.62\n",
       "6            7          Astrid Gruber                    38        42.62\n",
       "43          44       Terhi Hämäläinen                    38        41.62\n",
       "42          43       Isabelle Mercier                    38        40.62\n",
       "4            5  František Wichterlová                    38        40.62\n",
       "47          48  Johannes Van der Berg                    38        40.62\n",
       "19          20             Dan Miller                    38        39.62\n",
       "41          42           Wyatt Girard                    38        39.62\n",
       "33          34         João Fernandes                    38        39.62\n",
       "21          22        Heather Leacock                    38        39.62\n",
       "0            1         Luís Gonçalves                    38        39.62\n",
       "16          17             Jack Smith                    38        39.62\n",
       "2            3      François Tremblay                    38        39.62\n",
       "3            4           Bjørn Hansen                    38        39.62\n",
       "38          39        Camille Bernard                    38        38.62\n",
       "57          58           Manoj Pareek                    38        38.62\n",
       "39          40     Dominique Lefebvre                    38        38.62\n",
       "18          19              Tim Goyer                    38        38.62\n",
       "14          15      Jennifer Peterson                    38        38.62\n",
       "50          51       Joakim Johansson                    38        38.62\n",
       "46          47          Lucas Mancini                    38        37.62\n",
       "52          53            Phil Hughes                    38        37.62\n",
       "48          49       Stanislaw Wójcik                    38        37.62\n",
       "54          55            Mark Taylor                    38        37.62\n",
       "55          56        Diego Gutiérrez                    38        37.62\n",
       "51          52             Emma Jones                    38        37.62\n",
       "40          41            Marc Dubois                    38        37.62\n",
       "49          50          Enrique Muñoz                    38        37.62\n",
       "53          54           Steve Murray                    38        37.62\n",
       "29          30         Edward Francis                    38        37.62\n",
       "37          38        Niklas Schröder                    38        37.62\n",
       "17          18        Michelle Brooks                    38        37.62\n",
       "7            8           Daan Peeters                    38        37.62\n",
       "8            9           Kara Nielsen                    38        37.62\n",
       "9           10        Eduardo Martins                    38        37.62\n",
       "10          11        Alexandre Rocha                    38        37.62\n",
       "11          12        Roberto Almeida                    38        37.62\n",
       "12          13         Fernanda Ramos                    38        37.62\n",
       "13          14           Mark Philips                    38        37.62\n",
       "15          16           Frank Harris                    38        37.62\n",
       "20          21            Kathy Chase                    38        37.62\n",
       "35          36       Hannah Schneider                    38        37.62\n",
       "22          23            John Gordon                    38        37.62\n",
       "26          27           Patrick Gray                    38        37.62\n",
       "28          29           Robert Brown                    38        37.62\n",
       "1            2          Leonie Köhler                    38        37.62\n",
       "30          31            Martha Silk                    38        37.62\n",
       "31          32         Aaron Mitchell                    38        37.62\n",
       "32          33         Ellie Sullivan                    38        37.62\n",
       "34          35       Madalena Sampaio                    38        37.62\n",
       "58          59        Puja Srivastava                    36        36.64"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "req_4 = pd.merge(df_FactSales, df_DimCustomer, how='inner', on='CustomerId')\n",
    "\n",
    "# Group by 'CustomerId' and 'CustomerName', and calculate count of 'InvoiceId' and sum of 'ExtendedPrice'\n",
    "req_4 = req_4.groupby(['CustomerId', 'CustomerName']).agg({\n",
    "    'InvoiceId': 'count',\n",
    "    'ExtendedPrice': 'sum'\n",
    "}).reset_index()\n",
    "req_4.rename(columns={'InvoiceId': 'Total No of invoices', 'ExtendedPrice': 'Total Sales'}, inplace=True)\n",
    "req_4 = req_4.sort_values('Total Sales', ascending=False)\n",
    "req_4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>EmployeeId</th>\n",
       "      <th>EmployeeName</th>\n",
       "      <th>Total Invoices</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>Jane Peacock</td>\n",
       "      <td>796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>Margaret Park</td>\n",
       "      <td>760</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>Steve Johnson</td>\n",
       "      <td>684</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   EmployeeId   EmployeeName  Total Invoices\n",
       "0           3   Jane Peacock             796\n",
       "1           4  Margaret Park             760\n",
       "2           5  Steve Johnson             684"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "req_5 = pd.merge(df_DimEmployee, df_FactSales, how='right', on='EmployeeId')\n",
    "\n",
    "# Group by 'EmployeeId' and 'EmployeeName', and calculate count of 'InvoiceId'\n",
    "req_5 = req_5.groupby(['EmployeeId', 'EmployeeName']).agg({'InvoiceId': 'count'}).reset_index()\n",
    "req_5.rename(columns={'InvoiceId': 'Total Invoices'}, inplace=True)\n",
    "req_5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "req_5_time = pd.merge(df_FactSales, df_DimEmployee, how='inner', on='EmployeeId')\n",
    "req_5_time = pd.merge(req_5_time, df_DimDate, how='inner', left_on='InvoiceDateID', right_on='DateID')\n",
    "\n",
    "# Group by 'EmployeeId', 'EmployeeName', 'FullDate', 'MonthofYear', 'Year', and calculate count of 'InvoiceID'\n",
    "req_5_time = req_5_time.groupby(['EmployeeId', 'EmployeeName', 'FullDate', 'MonthOfYear', 'Year']).agg({'InvoiceId': 'count'}).reset_index()\n",
    "req_5_time.rename(columns={'InvoiceId': 'Total Invoices'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>EmployeeId</th>\n",
       "      <th>EmployeeName</th>\n",
       "      <th>MonthOfYear</th>\n",
       "      <th>Year</th>\n",
       "      <th>Total_Invoices</th>\n",
       "      <th>Total_Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>Jane Peacock</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>3</td>\n",
       "      <td>Jane Peacock</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>4</td>\n",
       "      <td>3.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>3</td>\n",
       "      <td>Jane Peacock</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>3</td>\n",
       "      <td>Jane Peacock</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>10</td>\n",
       "      <td>9.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>3</td>\n",
       "      <td>Jane Peacock</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>20</td>\n",
       "      <td>19.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>5</td>\n",
       "      <td>Steve Johnson</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>16</td>\n",
       "      <td>15.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>5</td>\n",
       "      <td>Steve Johnson</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>5</td>\n",
       "      <td>4.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>5</td>\n",
       "      <td>Steve Johnson</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>19</td>\n",
       "      <td>18.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>5</td>\n",
       "      <td>Steve Johnson</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>9</td>\n",
       "      <td>8.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170</th>\n",
       "      <td>5</td>\n",
       "      <td>Steve Johnson</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>6</td>\n",
       "      <td>5.94</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>171 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     EmployeeId   EmployeeName  MonthOfYear    Year  Total_Invoices  \\\n",
       "0             3   Jane Peacock          1.0  2009.0               1   \n",
       "11            3   Jane Peacock          1.0  2010.0               4   \n",
       "23            3   Jane Peacock          1.0  2011.0               2   \n",
       "35            3   Jane Peacock          1.0  2012.0              10   \n",
       "47            3   Jane Peacock          1.0  2013.0              20   \n",
       "..          ...            ...          ...     ...             ...   \n",
       "126           5  Steve Johnson         12.0  2009.0              16   \n",
       "136           5  Steve Johnson         12.0  2010.0               5   \n",
       "148           5  Steve Johnson         12.0  2011.0              19   \n",
       "160           5  Steve Johnson         12.0  2012.0               9   \n",
       "170           5  Steve Johnson         12.0  2013.0               6   \n",
       "\n",
       "     Total_Sales  \n",
       "0           0.99  \n",
       "11          3.96  \n",
       "23          1.98  \n",
       "35          9.90  \n",
       "47         19.80  \n",
       "..           ...  \n",
       "126        15.84  \n",
       "136         4.95  \n",
       "148        18.81  \n",
       "160         8.91  \n",
       "170         5.94  \n",
       "\n",
       "[171 rows x 6 columns]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_temp = pd.merge(df_FactSales, df_DimDate, how='inner', left_on='InvoiceDateID', right_on='DateID')\n",
    "\n",
    "# Group by 'EmployeeId', 'Year', 'MonthOfYear', and calculate count of 'InvoiceId' and sum of 'ExtendedPrice'\n",
    "df_temp = df_temp.groupby(['EmployeeId', 'Year', 'MonthOfYear']).agg({\n",
    "    'InvoiceId': 'count',\n",
    "    'ExtendedPrice': 'sum'\n",
    "}).reset_index()\n",
    "df_temp.rename(columns={'InvoiceId': 'Total_Invoices', 'ExtendedPrice': 'Total_Sales'}, inplace=True)\n",
    "\n",
    "# Merge with the 'DimEmployee' DataFrame on 'EmployeeId'\n",
    "req_5_year = pd.merge(df_temp, df_DimEmployee, how='inner', on='EmployeeId')\n",
    "req_5_year = req_5_year[['EmployeeId', 'EmployeeName', 'MonthOfYear', 'Year', 'Total_Invoices', 'Total_Sales']].sort_values(['EmployeeId', 'MonthOfYear', 'Year'])\n",
    "req_5_year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>EmployeeId</th>\n",
       "      <th>EmployeeName</th>\n",
       "      <th>Total_Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>Jane Peacock</td>\n",
       "      <td>833.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>Margaret Park</td>\n",
       "      <td>775.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>Steve Johnson</td>\n",
       "      <td>720.16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   EmployeeId   EmployeeName  Total_Sales\n",
       "0           3   Jane Peacock       833.04\n",
       "1           4  Margaret Park       775.40\n",
       "2           5  Steve Johnson       720.16"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "req_6 = pd.merge(df_DimEmployee, df_FactSales, how='right', on='EmployeeId')\n",
    "\n",
    "# Group by 'EmployeeId' and 'EmployeeName', and calculate sum of 'ExtendedPrice'\n",
    "req_6 = req_6.groupby(['EmployeeId', 'EmployeeName']).agg({'ExtendedPrice': 'sum'}).reset_index()\n",
    "req_6.rename(columns={'ExtendedPrice': 'Total_Sales'}, inplace=True)\n",
    "req_6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>EmployeeId</th>\n",
       "      <th>EmployeeName</th>\n",
       "      <th>FullDate</th>\n",
       "      <th>MonthOfYear</th>\n",
       "      <th>Year</th>\n",
       "      <th>Total_Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>Jane Peacock</td>\n",
       "      <td>2009-01-19</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>Jane Peacock</td>\n",
       "      <td>2009-02-01</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>1.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Jane Peacock</td>\n",
       "      <td>2009-02-02</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>3.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Jane Peacock</td>\n",
       "      <td>2009-02-03</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>5.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>Jane Peacock</td>\n",
       "      <td>2009-02-06</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>8.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>398</th>\n",
       "      <td>5</td>\n",
       "      <td>Steve Johnson</td>\n",
       "      <td>2013-10-21</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>399</th>\n",
       "      <td>5</td>\n",
       "      <td>Steve Johnson</td>\n",
       "      <td>2013-11-05</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>5.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>400</th>\n",
       "      <td>5</td>\n",
       "      <td>Steve Johnson</td>\n",
       "      <td>2013-11-13</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>25.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>401</th>\n",
       "      <td>5</td>\n",
       "      <td>Steve Johnson</td>\n",
       "      <td>2013-12-04</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>1.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>402</th>\n",
       "      <td>5</td>\n",
       "      <td>Steve Johnson</td>\n",
       "      <td>2013-12-05</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>3.96</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>403 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     EmployeeId   EmployeeName    FullDate  MonthOfYear    Year  Total_Sales\n",
       "0             3   Jane Peacock  2009-01-19          1.0  2009.0         0.99\n",
       "1             3   Jane Peacock  2009-02-01          2.0  2009.0         1.98\n",
       "2             3   Jane Peacock  2009-02-02          2.0  2009.0         3.96\n",
       "3             3   Jane Peacock  2009-02-03          2.0  2009.0         5.94\n",
       "4             3   Jane Peacock  2009-02-06          2.0  2009.0         8.91\n",
       "..          ...            ...         ...          ...     ...          ...\n",
       "398           5  Steve Johnson  2013-10-21         10.0  2013.0         0.99\n",
       "399           5  Steve Johnson  2013-11-05         11.0  2013.0         5.94\n",
       "400           5  Steve Johnson  2013-11-13         11.0  2013.0        25.86\n",
       "401           5  Steve Johnson  2013-12-04         12.0  2013.0         1.98\n",
       "402           5  Steve Johnson  2013-12-05         12.0  2013.0         3.96\n",
       "\n",
       "[403 rows x 6 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "req_6_time = pd.merge(df_FactSales, df_DimEmployee, how='inner', on='EmployeeId')\n",
    "req_6_time = pd.merge(req_6_time, df_DimDate, how='inner', left_on='InvoiceDateID', right_on='DateID')\n",
    "\n",
    "# Group by 'EmployeeId', 'EmployeeName', 'FullDate', 'MonthofYear', 'Year', and calculate sum of 'ExtendedPrice'\n",
    "req_6_time = req_6_time.groupby(['EmployeeId', 'EmployeeName', 'FullDate', 'MonthOfYear', 'Year']).agg({'ExtendedPrice': 'sum'}).reset_index()\n",
    "req_6_time.rename(columns={'ExtendedPrice': 'Total_Sales'}, inplace=True)\n",
    "req_6_time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>EmployeeId</th>\n",
       "      <th>EmployeeName</th>\n",
       "      <th>MonthOfYear</th>\n",
       "      <th>Year</th>\n",
       "      <th>Total_Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>Jane Peacock</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>3</td>\n",
       "      <td>Jane Peacock</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>3.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>3</td>\n",
       "      <td>Jane Peacock</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>1.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>3</td>\n",
       "      <td>Jane Peacock</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>9.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>3</td>\n",
       "      <td>Jane Peacock</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>19.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>5</td>\n",
       "      <td>Steve Johnson</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>15.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>5</td>\n",
       "      <td>Steve Johnson</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>4.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>5</td>\n",
       "      <td>Steve Johnson</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>18.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>5</td>\n",
       "      <td>Steve Johnson</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>8.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170</th>\n",
       "      <td>5</td>\n",
       "      <td>Steve Johnson</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>5.94</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>171 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     EmployeeId   EmployeeName  MonthOfYear    Year  Total_Sales\n",
       "0             3   Jane Peacock          1.0  2009.0         0.99\n",
       "11            3   Jane Peacock          1.0  2010.0         3.96\n",
       "23            3   Jane Peacock          1.0  2011.0         1.98\n",
       "35            3   Jane Peacock          1.0  2012.0         9.90\n",
       "47            3   Jane Peacock          1.0  2013.0        19.80\n",
       "..          ...            ...          ...     ...          ...\n",
       "126           5  Steve Johnson         12.0  2009.0        15.84\n",
       "136           5  Steve Johnson         12.0  2010.0         4.95\n",
       "148           5  Steve Johnson         12.0  2011.0        18.81\n",
       "160           5  Steve Johnson         12.0  2012.0         8.91\n",
       "170           5  Steve Johnson         12.0  2013.0         5.94\n",
       "\n",
       "[171 rows x 5 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_temp = pd.merge(df_FactSales, df_DimDate, how='inner', left_on='InvoiceDateID', right_on='DateID')\n",
    "\n",
    "# Group by 'EmployeeId', 'Year', 'MonthOfYear', and calculate sum of 'ExtendedPrice'\n",
    "df_temp = df_temp.groupby(['EmployeeId', 'Year', 'MonthOfYear']).agg({'ExtendedPrice': 'sum'}).reset_index()\n",
    "df_temp.rename(columns={'ExtendedPrice': 'Total_Sales'}, inplace=True)\n",
    "\n",
    "# Merge with the 'DimEmployee' DataFrame on 'EmployeeId'\n",
    "req_6_year = pd.merge(df_temp, df_DimEmployee, how='inner', on='EmployeeId')\n",
    "req_6_year = req_6_year[['EmployeeId', 'EmployeeName', 'MonthOfYear', 'Year', 'Total_Sales']].sort_values(['EmployeeId', 'MonthOfYear', 'Year'])\n",
    "req_6_year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>GenreId</th>\n",
       "      <th>FullDate</th>\n",
       "      <th>Total Invoices</th>\n",
       "      <th>Total Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2009-01-01</td>\n",
       "      <td>2</td>\n",
       "      <td>3.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>4</td>\n",
       "      <td>15.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2009-01-03</td>\n",
       "      <td>6</td>\n",
       "      <td>35.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2009-01-06</td>\n",
       "      <td>9</td>\n",
       "      <td>44.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>2009-02-11</td>\n",
       "      <td>14</td>\n",
       "      <td>97.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>706</th>\n",
       "      <td>24</td>\n",
       "      <td>2012-10-14</td>\n",
       "      <td>1</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>707</th>\n",
       "      <td>24</td>\n",
       "      <td>2012-10-27</td>\n",
       "      <td>8</td>\n",
       "      <td>15.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>708</th>\n",
       "      <td>24</td>\n",
       "      <td>2012-10-28</td>\n",
       "      <td>4</td>\n",
       "      <td>15.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709</th>\n",
       "      <td>24</td>\n",
       "      <td>2012-10-29</td>\n",
       "      <td>6</td>\n",
       "      <td>11.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>710</th>\n",
       "      <td>24</td>\n",
       "      <td>2012-11-01</td>\n",
       "      <td>9</td>\n",
       "      <td>35.64</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>711 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     GenreId    FullDate  Total Invoices  Total Sales\n",
       "0          1  2009-01-01               2         3.96\n",
       "1          1  2009-01-02               4        15.84\n",
       "2          1  2009-01-03               6        35.64\n",
       "3          1  2009-01-06               9        44.55\n",
       "4          1  2009-02-11              14        97.02\n",
       "..       ...         ...             ...          ...\n",
       "706       24  2012-10-14               1         0.99\n",
       "707       24  2012-10-27               8        15.84\n",
       "708       24  2012-10-28               4        15.84\n",
       "709       24  2012-10-29               6        11.88\n",
       "710       24  2012-11-01               9        35.64\n",
       "\n",
       "[711 rows x 4 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_temp = pd.merge(df_FactGenre, df_FactSales, how='inner', left_on='OrderDateID', right_on='InvoiceDateID')\n",
    "df_temp = pd.merge(df_temp, df_DimDate, how='inner', left_on='OrderDateID', right_on='DateID')\n",
    "\n",
    "# Group by 'GenreId', 'FullDate', and calculate count of 'InvoiceId' and sum of 'SumPrice'\n",
    "req_7_day = df_temp.groupby(['GenreId', 'FullDate']).agg({'InvoiceId': 'count', 'SumPrice': 'sum'}).reset_index()\n",
    "req_7_day.rename(columns={'InvoiceId': 'Total Invoices', 'SumPrice': 'Total Sales'}, inplace=True)\n",
    "req_7_day\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>GenreId</th>\n",
       "      <th>MonthOfYear</th>\n",
       "      <th>Total Invoices</th>\n",
       "      <th>Total Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>117</td>\n",
       "      <td>620.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>155</td>\n",
       "      <td>869.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>111</td>\n",
       "      <td>450.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>4.0</td>\n",
       "      <td>131</td>\n",
       "      <td>591.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>5.0</td>\n",
       "      <td>156</td>\n",
       "      <td>563.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>177</th>\n",
       "      <td>24</td>\n",
       "      <td>4.0</td>\n",
       "      <td>18</td>\n",
       "      <td>43.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178</th>\n",
       "      <td>24</td>\n",
       "      <td>6.0</td>\n",
       "      <td>14</td>\n",
       "      <td>69.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179</th>\n",
       "      <td>24</td>\n",
       "      <td>7.0</td>\n",
       "      <td>15</td>\n",
       "      <td>32.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180</th>\n",
       "      <td>24</td>\n",
       "      <td>10.0</td>\n",
       "      <td>33</td>\n",
       "      <td>72.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181</th>\n",
       "      <td>24</td>\n",
       "      <td>11.0</td>\n",
       "      <td>9</td>\n",
       "      <td>35.64</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>182 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     GenreId  MonthOfYear  Total Invoices  Total Sales\n",
       "0          1          1.0             117       620.73\n",
       "1          1          2.0             155       869.22\n",
       "2          1          3.0             111       450.45\n",
       "3          1          4.0             131       591.03\n",
       "4          1          5.0             156       563.31\n",
       "..       ...          ...             ...          ...\n",
       "177       24          4.0              18        43.56\n",
       "178       24          6.0              14        69.30\n",
       "179       24          7.0              15        32.67\n",
       "180       24         10.0              33        72.27\n",
       "181       24         11.0               9        35.64\n",
       "\n",
       "[182 rows x 4 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "req_7_month = df_temp.groupby(['GenreId', 'MonthOfYear']).agg({'InvoiceId': 'count', 'SumPrice': 'sum'}).reset_index()\n",
    "req_7_month.rename(columns={'InvoiceId': 'Total Invoices', 'SumPrice': 'Total Sales'}, inplace=True)\n",
    "req_7_month\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>GenreId</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>Total Invoices</th>\n",
       "      <th>Total Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>383</td>\n",
       "      <td>1940.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>444</td>\n",
       "      <td>1786.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>438</td>\n",
       "      <td>1963.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>4.0</td>\n",
       "      <td>410</td>\n",
       "      <td>1976.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>111</td>\n",
       "      <td>238.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>23</td>\n",
       "      <td>4.0</td>\n",
       "      <td>14</td>\n",
       "      <td>55.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>24</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14</td>\n",
       "      <td>55.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>24</td>\n",
       "      <td>2.0</td>\n",
       "      <td>32</td>\n",
       "      <td>112.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>24</td>\n",
       "      <td>3.0</td>\n",
       "      <td>15</td>\n",
       "      <td>32.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>24</td>\n",
       "      <td>4.0</td>\n",
       "      <td>42</td>\n",
       "      <td>107.91</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>92 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    GenreId  Quarter  Total Invoices  Total Sales\n",
       "0         1      1.0             383      1940.40\n",
       "1         1      2.0             444      1786.95\n",
       "2         1      3.0             438      1963.17\n",
       "3         1      4.0             410      1976.04\n",
       "4         2      1.0             111       238.59\n",
       "..      ...      ...             ...          ...\n",
       "87       23      4.0              14        55.44\n",
       "88       24      1.0              14        55.44\n",
       "89       24      2.0              32       112.86\n",
       "90       24      3.0              15        32.67\n",
       "91       24      4.0              42       107.91\n",
       "\n",
       "[92 rows x 4 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "req_7_quarter = df_temp.groupby(['GenreId', 'Quarter']).agg({'InvoiceId': 'count', 'SumPrice': 'sum'}).reset_index()\n",
    "req_7_quarter.rename(columns={'InvoiceId': 'Total Invoices', 'SumPrice': 'Total Sales'}, inplace=True)\n",
    "req_7_quarter\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>GenreId</th>\n",
       "      <th>Year</th>\n",
       "      <th>Total Invoices</th>\n",
       "      <th>Total Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>360</td>\n",
       "      <td>1590.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>333</td>\n",
       "      <td>1341.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>303</td>\n",
       "      <td>1544.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>340</td>\n",
       "      <td>1506.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>339</td>\n",
       "      <td>1683.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>23</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>14</td>\n",
       "      <td>55.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>23</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>14</td>\n",
       "      <td>55.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>24</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>32</td>\n",
       "      <td>99.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>24</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>29</td>\n",
       "      <td>101.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>24</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>42</td>\n",
       "      <td>107.91</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>104 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     GenreId    Year  Total Invoices  Total Sales\n",
       "0          1  2009.0             360      1590.93\n",
       "1          1  2010.0             333      1341.45\n",
       "2          1  2011.0             303      1544.40\n",
       "3          1  2012.0             340      1506.78\n",
       "4          1  2013.0             339      1683.00\n",
       "..       ...     ...             ...          ...\n",
       "99        23  2011.0              14        55.44\n",
       "100       23  2012.0              14        55.44\n",
       "101       24  2010.0              32        99.00\n",
       "102       24  2011.0              29       101.97\n",
       "103       24  2012.0              42       107.91\n",
       "\n",
       "[104 rows x 4 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "req_7_year = df_temp.groupby(['GenreId', 'Year']).agg({'InvoiceId': 'count', 'SumPrice': 'sum'}).reset_index()\n",
    "req_7_year.rename(columns={'InvoiceId': 'Total Invoices', 'SumPrice': 'Total Sales'}, inplace=True)\n",
    "req_7_year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "req_9_avg = pd.merge(df_FactGenre, df_DimCustomer, how='inner', on='CustomerId')\n",
    "\n",
    "# Group by 'CustomerId' and 'CustomerName', and calculate the average of 'NumberOfDaysReturned'\n",
    "req_9_avg = req_9_avg.groupby(['CustomerId', 'CustomerName']).agg({'NumberOfDaysReturned': 'mean'}).reset_index()\n",
    "req_9_avg.rename(columns={'NumberOfDaysReturned': 'Average Days returned'}, inplace=True)\n",
    "req_9_avg = req_9_avg[['CustomerId', 'CustomerName', 'Average Days returned']].sort_values('CustomerId')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CustomerName</th>\n",
       "      <th>OrderDateID</th>\n",
       "      <th>NumberOfDaysReturned</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Luís Gonçalves</td>\n",
       "      <td>20130807</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Leonie Köhler</td>\n",
       "      <td>20120713</td>\n",
       "      <td>421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>François Tremblay</td>\n",
       "      <td>20130920</td>\n",
       "      <td>1248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bjørn Hansen</td>\n",
       "      <td>20131003</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>František Wichterlová</td>\n",
       "      <td>20130506</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Helena Holý</td>\n",
       "      <td>20131113</td>\n",
       "      <td>908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Astrid Gruber</td>\n",
       "      <td>20130619</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Daan Peeters</td>\n",
       "      <td>20131004</td>\n",
       "      <td>678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Kara Nielsen</td>\n",
       "      <td>20130202</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Eduardo Martins</td>\n",
       "      <td>20130812</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Alexandre Rocha</td>\n",
       "      <td>20130318</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Roberto Almeida</td>\n",
       "      <td>20131005</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Fernanda Ramos</td>\n",
       "      <td>20121101</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Mark Philips</td>\n",
       "      <td>20130511</td>\n",
       "      <td>908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Jennifer Peterson</td>\n",
       "      <td>20121215</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Frank Harris</td>\n",
       "      <td>20130704</td>\n",
       "      <td>1015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Jack Smith</td>\n",
       "      <td>20120731</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Michelle Brooks</td>\n",
       "      <td>20131008</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Tim Goyer</td>\n",
       "      <td>20120913</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Dan Miller</td>\n",
       "      <td>20131121</td>\n",
       "      <td>1005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Kathy Chase</td>\n",
       "      <td>20131204</td>\n",
       "      <td>584</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Heather Leacock</td>\n",
       "      <td>20130707</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>John Gordon</td>\n",
       "      <td>20131204</td>\n",
       "      <td>773</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Frank Ralston</td>\n",
       "      <td>20130820</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Victor Stevens</td>\n",
       "      <td>20131205</td>\n",
       "      <td>678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Richard Cunningham</td>\n",
       "      <td>20130405</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Patrick Gray</td>\n",
       "      <td>20131013</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Julia Barnett</td>\n",
       "      <td>20130519</td>\n",
       "      <td>421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Robert Brown</td>\n",
       "      <td>20131206</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Edward Francis</td>\n",
       "      <td>20130102</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Martha Silk</td>\n",
       "      <td>20130712</td>\n",
       "      <td>1002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Aaron Mitchell</td>\n",
       "      <td>20130215</td>\n",
       "      <td>421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Ellie Sullivan</td>\n",
       "      <td>20130904</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>João Fernandes</td>\n",
       "      <td>20121001</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Madalena Sampaio</td>\n",
       "      <td>20131209</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Hannah Schneider</td>\n",
       "      <td>20121114</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Fynn Zimmermann</td>\n",
       "      <td>20130603</td>\n",
       "      <td>1015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Niklas Schröder</td>\n",
       "      <td>20120630</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Camille Bernard</td>\n",
       "      <td>20130907</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Dominique Lefebvre</td>\n",
       "      <td>20120813</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Marc Dubois</td>\n",
       "      <td>20131021</td>\n",
       "      <td>327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Wyatt Girard</td>\n",
       "      <td>20131103</td>\n",
       "      <td>827</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Isabelle Mercier</td>\n",
       "      <td>20130606</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Terhi Hämäläinen</td>\n",
       "      <td>20131214</td>\n",
       "      <td>581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Ladislav Kovács</td>\n",
       "      <td>20130720</td>\n",
       "      <td>327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Hugh O'Reilly</td>\n",
       "      <td>20131104</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Lucas Mancini</td>\n",
       "      <td>20130305</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>Johannes Van der Berg</td>\n",
       "      <td>20130912</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>Stanislaw Wójcik</td>\n",
       "      <td>20130418</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Enrique Muñoz</td>\n",
       "      <td>20131105</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Joakim Johansson</td>\n",
       "      <td>20121202</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>Emma Jones</td>\n",
       "      <td>20130611</td>\n",
       "      <td>814</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>Phil Hughes</td>\n",
       "      <td>20130115</td>\n",
       "      <td>1005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>Steve Murray</td>\n",
       "      <td>20130804</td>\n",
       "      <td>1015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>Mark Taylor</td>\n",
       "      <td>20120831</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>Diego Gutiérrez</td>\n",
       "      <td>20131108</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>Luis Rojas</td>\n",
       "      <td>20121014</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>Manoj Pareek</td>\n",
       "      <td>20131222</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>Puja Srivastava</td>\n",
       "      <td>20120530</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             CustomerName  OrderDateID  NumberOfDaysReturned\n",
       "0          Luís Gonçalves     20130807                   243\n",
       "1           Leonie Köhler     20120713                   421\n",
       "2       François Tremblay     20130920                  1248\n",
       "3            Bjørn Hansen     20131003                     0\n",
       "4   František Wichterlová     20130506                   243\n",
       "5             Helena Holý     20131113                   908\n",
       "6           Astrid Gruber     20130619                   233\n",
       "7            Daan Peeters     20131004                   678\n",
       "8            Kara Nielsen     20130202                   243\n",
       "9         Eduardo Martins     20130812                    41\n",
       "10        Alexandre Rocha     20130318                   233\n",
       "11        Roberto Almeida     20131005                    94\n",
       "12         Fernanda Ramos     20121101                   243\n",
       "13           Mark Philips     20130511                   908\n",
       "14      Jennifer Peterson     20121215                   233\n",
       "15           Frank Harris     20130704                  1015\n",
       "16             Jack Smith     20120731                   243\n",
       "17        Michelle Brooks     20131008                   243\n",
       "18              Tim Goyer     20120913                     0\n",
       "19             Dan Miller     20131121                  1005\n",
       "20            Kathy Chase     20131204                   584\n",
       "21        Heather Leacock     20130707                   243\n",
       "22            John Gordon     20131204                   773\n",
       "23          Frank Ralston     20130820                     0\n",
       "24         Victor Stevens     20131205                   678\n",
       "25     Richard Cunningham     20130405                   243\n",
       "26           Patrick Gray     20131013                    41\n",
       "27          Julia Barnett     20130519                   421\n",
       "28           Robert Brown     20131206                    94\n",
       "29         Edward Francis     20130102                   243\n",
       "30            Martha Silk     20130712                  1002\n",
       "31         Aaron Mitchell     20130215                   421\n",
       "32         Ellie Sullivan     20130904                    94\n",
       "33         João Fernandes     20121001                   243\n",
       "34       Madalena Sampaio     20131209                   243\n",
       "35       Hannah Schneider     20121114                   233\n",
       "36        Fynn Zimmermann     20130603                  1015\n",
       "37        Niklas Schröder     20120630                   243\n",
       "38        Camille Bernard     20130907                   243\n",
       "39     Dominique Lefebvre     20120813                   233\n",
       "40            Marc Dubois     20131021                   327\n",
       "41           Wyatt Girard     20131103                   827\n",
       "42       Isabelle Mercier     20130606                   243\n",
       "43       Terhi Hämäläinen     20131214                   581\n",
       "44        Ladislav Kovács     20130720                   327\n",
       "45          Hugh O'Reilly     20131104                     0\n",
       "46          Lucas Mancini     20130305                   243\n",
       "47  Johannes Van der Berg     20130912                    41\n",
       "48       Stanislaw Wójcik     20130418                   233\n",
       "49          Enrique Muñoz     20131105                    94\n",
       "50       Joakim Johansson     20121202                   243\n",
       "51             Emma Jones     20130611                   814\n",
       "52            Phil Hughes     20130115                  1005\n",
       "53           Steve Murray     20130804                  1015\n",
       "54            Mark Taylor     20120831                   243\n",
       "55        Diego Gutiérrez     20131108                   243\n",
       "56             Luis Rojas     20121014                     0\n",
       "57           Manoj Pareek     20131222                     0\n",
       "58        Puja Srivastava     20120530                   243"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_FactGenre_temp = df_FactGenre.copy()\n",
    "df_FactGenre_temp['rn'] = df_FactGenre.groupby('CustomerId')['OrderDateID'].rank(ascending=False, method='first')\n",
    "\n",
    "# Filter the DataFrame to keep only the rows where rn is 1\n",
    "df_temp = df_FactGenre_temp[df_FactGenre_temp['rn'] == 1][['CustomerId', 'OrderDateID', 'NumberOfDaysReturned']]\n",
    "\n",
    "# Merge with the 'DimCustomer' DataFrame on 'CustomerId'\n",
    "req_9_last = pd.merge(df_temp, df_DimCustomer, how='inner', on='CustomerId')\n",
    "req_9_last = req_9_last[['CustomerName', 'OrderDateID', 'NumberOfDaysReturned']]\n",
    "req_9_last"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TrackId</th>\n",
       "      <th>GenreId</th>\n",
       "      <th>AlbumId</th>\n",
       "      <th>PlaylistTrackViews</th>\n",
       "      <th>Milliseconds</th>\n",
       "      <th>SumTimeViews</th>\n",
       "      <th>IsCurrent</th>\n",
       "      <th>StartDate</th>\n",
       "      <th>EndDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2585</td>\n",
       "      <td>6</td>\n",
       "      <td>210</td>\n",
       "      <td>2</td>\n",
       "      <td>361978</td>\n",
       "      <td>723956</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2253</td>\n",
       "      <td>17</td>\n",
       "      <td>184</td>\n",
       "      <td>3</td>\n",
       "      <td>410409</td>\n",
       "      <td>1231227</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1921</td>\n",
       "      <td>7</td>\n",
       "      <td>158</td>\n",
       "      <td>3</td>\n",
       "      <td>275748</td>\n",
       "      <td>827244</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1589</td>\n",
       "      <td>1</td>\n",
       "      <td>128</td>\n",
       "      <td>2</td>\n",
       "      <td>258168</td>\n",
       "      <td>516336</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3249</td>\n",
       "      <td>20</td>\n",
       "      <td>253</td>\n",
       "      <td>2</td>\n",
       "      <td>2924007</td>\n",
       "      <td>5848014</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3498</th>\n",
       "      <td>2115</td>\n",
       "      <td>1</td>\n",
       "      <td>175</td>\n",
       "      <td>3</td>\n",
       "      <td>232359</td>\n",
       "      <td>697077</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3499</th>\n",
       "      <td>2164</td>\n",
       "      <td>1</td>\n",
       "      <td>178</td>\n",
       "      <td>3</td>\n",
       "      <td>377652</td>\n",
       "      <td>1132956</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3500</th>\n",
       "      <td>2656</td>\n",
       "      <td>1</td>\n",
       "      <td>215</td>\n",
       "      <td>3</td>\n",
       "      <td>261120</td>\n",
       "      <td>783360</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3501</th>\n",
       "      <td>1082</td>\n",
       "      <td>10</td>\n",
       "      <td>85</td>\n",
       "      <td>2</td>\n",
       "      <td>271020</td>\n",
       "      <td>542040</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3502</th>\n",
       "      <td>3246</td>\n",
       "      <td>20</td>\n",
       "      <td>253</td>\n",
       "      <td>2</td>\n",
       "      <td>2922088</td>\n",
       "      <td>5844176</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3503 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      TrackId  GenreId  AlbumId  PlaylistTrackViews  Milliseconds  \\\n",
       "0        2585        6      210                   2        361978   \n",
       "1        2253       17      184                   3        410409   \n",
       "2        1921        7      158                   3        275748   \n",
       "3        1589        1      128                   2        258168   \n",
       "4        3249       20      253                   2       2924007   \n",
       "...       ...      ...      ...                 ...           ...   \n",
       "3498     2115        1      175                   3        232359   \n",
       "3499     2164        1      178                   3        377652   \n",
       "3500     2656        1      215                   3        261120   \n",
       "3501     1082       10       85                   2        271020   \n",
       "3502     3246       20      253                   2       2922088   \n",
       "\n",
       "      SumTimeViews  IsCurrent  StartDate  EndDate  \n",
       "0           723956          1        NaN      NaN  \n",
       "1          1231227          1        NaN      NaN  \n",
       "2           827244          1        NaN      NaN  \n",
       "3           516336          1        NaN      NaN  \n",
       "4          5848014          1        NaN      NaN  \n",
       "...            ...        ...        ...      ...  \n",
       "3498        697077          1        NaN      NaN  \n",
       "3499       1132956          1        NaN      NaN  \n",
       "3500        783360          1        NaN      NaN  \n",
       "3501        542040          1        NaN      NaN  \n",
       "3502       5844176          1        NaN      NaN  \n",
       "\n",
       "[3503 rows x 9 columns]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_FactListen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CustomerCountry</th>\n",
       "      <th>Year</th>\n",
       "      <th>Total Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>USA</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>127.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>USA</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>103.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>USA</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>103.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>USA</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>102.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>USA</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>85.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>Poland</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Australia</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Finland</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>Spain</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>101 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerCountry    Year  Total Sales\n",
       "94             USA  2012.0       127.98\n",
       "91             USA  2009.0       103.95\n",
       "93             USA  2011.0       103.01\n",
       "92             USA  2010.0       102.98\n",
       "95             USA  2013.0        85.14\n",
       "..             ...     ...          ...\n",
       "78          Poland  2013.0         0.99\n",
       "4        Australia  2010.0         0.99\n",
       "39         Finland  2012.0         0.99\n",
       "1        Argentina  2011.0         0.99\n",
       "84           Spain  2009.0         0.99\n",
       "\n",
       "[101 rows x 3 columns]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "req_8 = pd.merge(df_FactSales, df_DimCustomer, how='inner', on='CustomerId')\n",
    "req_8 = pd.merge(req_8, df_DimDate, how='inner', left_on='InvoiceDateID', right_on='DateID')\n",
    "\n",
    "# Group by 'CustomerCountry', 'Year', and calculate sum of 'ExtendedPrice'\n",
    "req_8 = req_8.groupby(['CustomerCountry', 'Year']).agg({'ExtendedPrice': 'sum'}).reset_index()\n",
    "req_8.rename(columns={'ExtendedPrice': 'Total Sales'}, inplace=True)\n",
    "\n",
    "# Sort the DataFrame by 'Total Sales' in descending order\n",
    "req_8.sort_values('Total Sales', ascending=False, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>GenreName</th>\n",
       "      <th>Total Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Rock</td>\n",
       "      <td>826.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Latin</td>\n",
       "      <td>382.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Metal</td>\n",
       "      <td>261.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alternative &amp; Punk</td>\n",
       "      <td>241.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>TV Shows</td>\n",
       "      <td>93.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Jazz</td>\n",
       "      <td>79.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Blues</td>\n",
       "      <td>60.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Drama</td>\n",
       "      <td>57.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>R&amp;B/Soul</td>\n",
       "      <td>40.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Classical</td>\n",
       "      <td>40.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Sci Fi &amp; Fantasy</td>\n",
       "      <td>39.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Reggae</td>\n",
       "      <td>29.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Pop</td>\n",
       "      <td>27.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Soundtrack</td>\n",
       "      <td>19.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Comedy</td>\n",
       "      <td>17.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Hip Hop/Rap</td>\n",
       "      <td>16.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bossa Nova</td>\n",
       "      <td>14.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alternative</td>\n",
       "      <td>13.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>World</td>\n",
       "      <td>12.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Science Fiction</td>\n",
       "      <td>11.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Heavy Metal</td>\n",
       "      <td>11.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Electronica/Dance</td>\n",
       "      <td>11.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Easy Listening</td>\n",
       "      <td>9.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Rock And Roll</td>\n",
       "      <td>5.94</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             GenreName  Total Sales\n",
       "17                Rock       826.65\n",
       "12               Latin       382.14\n",
       "13               Metal       261.36\n",
       "1   Alternative & Punk       241.56\n",
       "22            TV Shows        93.53\n",
       "11                Jazz        79.20\n",
       "2                Blues        60.39\n",
       "6                Drama        57.71\n",
       "15            R&B/Soul        40.59\n",
       "4            Classical        40.59\n",
       "19    Sci Fi & Fantasy        39.80\n",
       "16              Reggae        29.70\n",
       "14                 Pop        27.72\n",
       "21          Soundtrack        19.80\n",
       "5               Comedy        17.91\n",
       "10         Hip Hop/Rap        16.83\n",
       "3           Bossa Nova        14.85\n",
       "0          Alternative        13.86\n",
       "23               World        12.87\n",
       "20     Science Fiction        11.94\n",
       "9          Heavy Metal        11.88\n",
       "8    Electronica/Dance        11.88\n",
       "7       Easy Listening         9.90\n",
       "18       Rock And Roll         5.94"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "req_10 = pd.merge(df_FactGenre, df_DimGenre, how='inner', on='GenreId')\n",
    "\n",
    "# Group by 'GenreName' and calculate sum of 'SumPrice'\n",
    "req_10 = req_10.groupby('GenreName').agg({'SumPrice': 'sum'}).reset_index()\n",
    "req_10.rename(columns={'SumPrice': 'Total Sales'}, inplace=True)\n",
    "\n",
    "# Sort the DataFrame by 'Total Sales' in descending order\n",
    "req_10.sort_values('Total Sales', ascending=False, inplace=True)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}