Misc SQL: Create monthly Allowance report and Fill Null against those month where emp has not received any allowances

Question: Generate a month wise allowance received report for each employee, keep Null in allowance for those month where employee has not received any allowance.

emp_id emp_name month_number allowance

E001 Aayansh         1         1000

E001 Aayansh         2         3000

E002 Rishika         3         2000

E002 Rishika         5         4000

Output should be :

month_number emp_id emp_name allowance

1 E001 Aayansh 1000

2 E001 Aayansh 3000

3 E001 Aayansh null

4 E001 Aayansh null

5 E001 Aayansh null

6 E001 Aayansh null

7 E001 Aayansh null

8 E001 Aayansh null

9 E001 Aayansh null

10 E001 Aayansh null

11 E001 Aayansh null

12 E001 Aayansh null

Solution: 

Steps:

1) generate a month number derived CTE

2) Cross join MonthCTE with EmpAllowance Table and take the distinct record

3) Left join the CorssJoin result from 2nd step to EmpAllowance table and take allowance from EmpAllowance

Query:

;with MonthCTE AS

(select 1 as month_number 

 union all 

 select month_number + 1 as month_number from MonthCTE 

 where month_number < 12

 ),

ReportTemplateCTE as 

(select DISTINCT m.month_number, e.emp_id, e.emp_name from MonthCTE m cross join EmpAllowance e)

 

select r.*, e.allowance from ReportTemplateCTE r left join EmpAllowance e

on r.month_number = e.month_number and r.emp_id = e.emp_id

order by r.emp_id

 

Post Reference: Vikram Aristocratic Elfin Share

Misc: Generate duplicate rows based on quantity - CTE

Ques: You have below records:

item_name     item_qty

Keyboard          3

Mouse             5

You need to duplicate each records depending upon the value of item_qty values.

Sol:

This can be done using CTE

;With DupCreationCTE(item_name, item_qty, item_counter) as

(

  select item_name, item_qty, 1 as item_counter from DupCreation

  union All

  select item_name, item_qty, item_counter + 1 as item_counter

  from DupCreationCTE where item_counter < item_qty

)

SELECT * from DupCreationCTE order by item_name, item_counter asc

Output:

item_name item_qty item_counter

"Keyboard" 3                 1

"Keyboard" 3                 2

"Keyboard" 3                 3

"Mouse"         5                 1

"Mouse"         5                 2

"Mouse"         5                 3

"Mouse"         5                 4

"Mouse"         5                 5

Post Reference: Vikram Aristocratic Elfin Share

176. Second Highest Salary

Table: Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
id is the primary key column for this table.
Each row of this table contains information about the salary of an employee.

 

Write an SQL query to report the second highest salary from the Employee table. If there is no second highest salary, the query should report null.

The query result format is in the following example.

 

Example 1:

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
Output: 
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

Example 2:

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
Output: 
+---------------------+
| SecondHighestSalary |
+---------------------+
| null                |
+---------------------+

Solution:

select salary as SecondHighestSalary from (
select salary, row_number() over (order by salary desc) as rno from Employee
    ) a
    where a.rno = 2

Post Reference: Vikram Aristocratic Elfin Share

175. Combine Two Tables

Table: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| personId    | int     |
| lastName    | varchar |
| firstName   | varchar |
+-------------+---------+
personId is the primary key column for this table.
This table contains information about the ID of some persons and their first and last names.

 

Table: Address

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| addressId   | int     |
| personId    | int     |
| city        | varchar |
| state       | varchar |
+-------------+---------+
addressId is the primary key column for this table.
Each row of this table contains information about the city and state of one person with ID = PersonId.

 

Write an SQL query to report the first name, last name, city, and state of each person in the Person table. If the address of a personId is not present in the Address table, report null instead.

Return the result table in any order.

The query result format is in the following example.

 

Example 1:

Input: 
Person table:
+----------+----------+-----------+
| personId | lastName | firstName |
+----------+----------+-----------+
| 1        | Wang     | Allen     |
| 2        | Alice    | Bob       |
+----------+----------+-----------+
Address table:
+-----------+----------+---------------+------------+
| addressId | personId | city          | state      |
+-----------+----------+---------------+------------+
| 1         | 2        | New York City | New York   |
| 2         | 3        | Leetcode      | California |
+-----------+----------+---------------+------------+
Output: 
+-----------+----------+---------------+----------+
| firstName | lastName | city          | state    |
+-----------+----------+---------------+----------+
| Allen     | Wang     | Null          | Null     |
| Bob       | Alice    | New York City | New York |
+-----------+----------+---------------+----------+
Explanation: 
There is no address in the address table for the personId = 1 so we return null in their city and state.
addressId = 1 contains information about the address of personId = 2.

Solution:

select p.firstname, p.lastname, a.city, a.state 
from Person p left join Address a 
on p.personid = a.personId

Post Reference: Vikram Aristocratic Elfin Share

Python Trick: Alternative to if-else/Case statement

Lets look the below code snippet

def add_number(a,b):
    print(a+b);

def multiply_number(a,b):
    print(a*b)

def division_number(a,b):
    print(a/b)

result = '30'

if result == '10':
    add_number(10,20)
elif result == '20':
    multiply_number(12,2)
elif result== '30':
    division_number(36,3)

here we have three method, and methods are called depending upon the value of result, if the result value is 10 then add_number is called, if result is 20 then multiply_number is called and so on

So you can see above we have written multiline if-elseif statement to call method on the bases of result value.

Now lets see the below code snippet, here we have declare a dictionary object with result value as a key of dictionary object and the associated method as a dictionary key value.

result_dict={
    '10':add_number,
    '20':multiply_number,
    '30':division_number
}

result='10'
result_dict[result](1,2)

The result value is store in a result variable and that variable is passed as an index to dictionary key which internally calls the associated method. So it just one line statement instead of if-else ladder.

Enjoy pythonic way J

Post Reference: Vikram Aristocratic Elfin Share

Python Trick: Make your program look simple and clean, with the help of dictionary:

When you have multiple object in you program, it often looks messy with different object name being used to call corresponding methods. Let’s see with an example:

class Dog:
    def __init__(self,name):
        self.talents=[]
        self.name=name
    def add_talent(self,talent):
        self.talents.append(talent)

dog_obj1 = Dog('Peddy')
dog_obj2 = Dog('Magnet')

dog_obj1.add_talent('Black Plet')
dog_obj1.add_talent('Fisher Dog')

dog_obj2.add_talent('Guard Dog')
dog_obj2.add_talent('Happy Eater')

print("Talent of Peddy")
print(dog_obj1.talents)

print("\nTalent of Magnet")
print(dog_obj1.talents)

#Output

Talent of Peddy

['Black Plet', 'Fisher Dog']

Talent of Magnet

['Black Plet', 'Fisher Dog']

here above if you see, we have Dog class which has a constructor which initialize two instance object talents (list obj) and  name and we have one instance method add_talent which add the talent of Dog object.

In later part of code, we have declared two object dog_obj1(‘Peddy’) and dog_obj2(‘Magnet’), and then we are calling add_talent method to add talent of Peddy and Magnet.

The code all ok, but just imagine when you have plenty of object in you program, then your program may look quite messy with different objects name.

So what could be the way?

Simple, simple create a dictionary with list of object key value pair and access each object instance variable and method with dictionary object key value. Look at below code snippet

# creating dictionary of Dog
obj_dict={
    'Peddy':dog_obj1,
    'Magnet':dog_obj2
}

# Accessing instance valriable of class
print(obj_dict['Peddy'].talents)
print(obj_dict['Magnet'].talents)

# To add new talent of Paddy, it would be qutie simple"
obj_dict['Peddy'].add_talent('Wolf mound')

#Printing talent of Peddy
print(obj_dict['Peddy'].talents)

#OUTPUT

['Black Plet', 'Fisher Dog']

['Guard Dog', 'Happy Eater']

['Black Plet', 'Fisher Dog', 'Wolf mound']

So here we created a dictionary object obj_dict with list of Dog class object with key value pair Peddy and Magnet. There after we are accessing the Peddy and Magnet talent with dictionary key

obj_dict['Peddy'].talents

obj_dict['Magnet'].talents

Now if we want to add new talent of Peddy then it quite simple:

obj_dict['Peddy'].add_talent('Wolf mound')

This way, your program looks very less messy and much readable.

Enjoy pythonic way J

Post Reference: Vikram Aristocratic Elfin Share

COVID19 India Data Analysis, Predicting Total Case on 4th of May (by end of lockdown Version-02)

Do check prev post: Covid19_StateWise_Analysis_India_Part1

Here we trying to focus on what will be the confirmed case count on the last day of lockdown version-02 in India, the entire analysis is based on growth rate technique.  Let’s import required modules

import numpy as np # linear algebra
import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)
import seaborn as sns
import matplotlib.pyplot as plt
%matplotlib inline
import plotly.express as px
import plotly.offline as py
import plotly.graph_objs as go
py.init_notebook_mode(connected=True)
import folium
import seaborn as sns
import os

import datetime

Let try to find out growth rate, considering the data from 30^th Jan

confirmed_df = pd.read_csv('https://raw.githubusercontent.com/CSSEGISandData/'+
                           'COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/'
                           +'time_series_covid19_confirmed_global.csv')

india_sel  = confirmed_df[confirmed_df['Country/Region']=='India'].loc[:'4/13/20']
india_confirmed_list = india_sel.values.tolist()[0]
india_confirmed_list[4]
growth_diff = []

for i in range(4,len(india_confirmed_list)):
    if (i == 4) or india_confirmed_list[i-1] == 0 :
        growth_diff.append(india_confirmed_list[i])
    else:
        growth_diff.append(india_confirmed_list[i] / india_confirmed_list[i-1])

growth_factor = sum(growth_diff)/len(growth_diff)
print('Average growth factor',growth_factor)

#OUTPUT: GROWTH RATE

Average growth factor 1.0637553331032963

Lets now calculate the next twenty 21 days case count and plot it in chart

x_axis_prediction_dt = []

dates = list(confirmed_df.columns[4:])
dates = list(pd.to_datetime(dates))

#we will add one day to the last day till which we have data
start_date = dates[len(dates) - 1]
for i in range(21):
    date = start_date + datetime.timedelta(days=1)
    x_axis_prediction_dt.append(date)
    start_date = date

# Get the last available day total number   
previous_day_cases = confirmed_df[confirmed_df['Country/Region']=='India'].iloc[:,-1]
# Converting series to float value
previous_day_cases = previous_day_cases.iloc[0]
y_axis_predicted_next21days_cases = []

for i in range(21):
    predicted_value = previous_day_cases *  growth_factor
    y_axis_predicted_next21days_cases.append(predicted_value)
    previous_day_cases = predicted_value
# print(previous_day_cases)

#add Graph
fig1=go.Figure()
fig1.add_trace(go.Scatter(x=x_axis_prediction_dt,
                          y=y_axis_predicted_next21days_cases,
                          name='India'
                               ))

fig1.layout.update(title_text='COVID-19 next twenty one prediction',xaxis_showgrid=False, yaxis_showgrid=False, width=800,
        height=500,font=dict(
#         family="Courier New, monospace",
        size=12,
        color="white"
    ))
fig1.layout.plot_bgcolor = 'Black'
fig1.layout.paper_bgcolor = 'Black'
fig1.show()

Growth rate predict cases will jump over 35k by 3rd of May

Post Reference: Vikram Aristocratic Elfin Share