📌 Data Science Portfolios, Speeding Up Python, KANs, and Other May Must-Reads

🗂 Category: DATA SCIENCE

🕒 Date: 2024-05-30 | ⏱️ Read time: 4 min read

The stories that resonated the most with our community in the past month

#Pandas #DataAnalysis #Python #DataScience #Tutorial

Top 30 Pandas Functions & Methods

This lesson covers 30 essential Pandas functions for data manipulation and analysis, each with a standalone example and its output.

---

1. pd.DataFrame()
Creates a new DataFrame (a 2D labeled data structure) from various inputs like dictionaries or lists.

import pandas as pd
data = {'col1': [1, 2], 'col2': [3, 4]}
df = pd.DataFrame(data)
print(df)

col1  col2
0     1     3
1     2     4

---

2. pd.Series()
Creates a new Series (a 1D labeled array).

import pandas as pd
s = pd.Series([10, 20, 30, 40], name='MyNumbers')
print(s)

0    10
1    20
2    30
3    40
Name: MyNumbers, dtype: int64

---

3. pd.read_csv()
Reads data from a CSV file into a DataFrame. (Assuming a file data.csv exists).

# Create a dummy csv file first
with open('data.csv', 'w') as f:
    f.write('Name,Age\nAlice,25\nBob,30')

df = pd.read_csv('data.csv')
print(df)

Name  Age
0  Alice   25
1    Bob   30

---

4. df.to_csv()
Writes a DataFrame to a CSV file.

import pandas as pd
df = pd.DataFrame({'Name': ['Charlie'], 'Age': [35]})
# index=False prevents writing the DataFrame index to the file
df.to_csv('output.csv', index=False)
# You can check that 'output.csv' has been created.
print("File 'output.csv' created.")

File 'output.csv' created.

#PandasIO #DataFrame #Series

---

5. df.head()
Returns the first n rows of the DataFrame (default is 5).

import pandas as pd
data = {'Name': ['A', 'B', 'C', 'D', 'E', 'F'], 'Value': [1, 2, 3, 4, 5, 6]}
df = pd.DataFrame(data)
print(df.head(3))

Name  Value
0    A      1
1    B      2
2    C      3

---

6. df.tail()
Returns the last n rows of the DataFrame (default is 5).

import pandas as pd
data = {'Name': ['A', 'B', 'C', 'D', 'E', 'F'], 'Value': [1, 2, 3, 4, 5, 6]}
df = pd.DataFrame(data)
print(df.tail(2))

Name  Value
4    E      5
5    F      6

---

7. df.info()
Provides a concise summary of the DataFrame, including data types and non-null values.

import pandas as pd
import numpy as np
data = {'col1': [1, 2, 3], 'col2': [4.0, 5.0, np.nan], 'col3': ['A', 'B', 'C']}
df = pd.DataFrame(data)
df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3 entries, 0 to 2
Data columns (total 3 columns):
 #   Column  Non-Null Count  Dtype  
---  ------  --------------  -----  
 0   col1    3 non-null      int64  
 1   col2    2 non-null      float64
 2   col3    3 non-null      object 
dtypes: float64(1), int64(1), object(1)
memory usage: 200.0+ bytes

---

8. df.shape
Returns a tuple representing the dimensionality (rows, columns) of the DataFrame.

import pandas as pd
df = pd.DataFrame({'A': [1, 2], 'B': [3, 4], 'C': [5, 6]})
print(df.shape)

(2, 3)

#DataInspection #PandasBasics

---

9. df.describe()
Generates descriptive statistics for numerical columns (count, mean, std, min, max, etc.).

import pandas as pd
df = pd.DataFrame({'Age': [22, 38, 26, 35, 29]})
print(df.describe())

Age
count   5.000000
mean   30.000000
std     6.363961
min    22.000000
25%    26.000000
50%    29.000000
75%    35.000000
max    38.000000

---

10. df.columns
Returns the column labels of the DataFrame.

import pandas as pd
df = pd.DataFrame({'Name': [], 'Age': [], 'City': []})
print(df.columns)

Index(['Name', 'Age', 'City'], dtype='object')

---

11. df.dtypes
Returns the data type of each column.

import pandas as pd
df = pd.DataFrame({'Name': ['Alice'], 'Age': [25], 'Salary': [75000.50]})
print(df.dtypes)

Name       object
Age         int64
Salary    float64
dtype: object

---

12. Selecting a Column
Select a single column, which returns a Pandas Series.

import pandas as pd
data = {'Name': ['Alice', 'Bob'], 'Age': [25, 30]}
df = pd.DataFrame(data)
ages = df['Age']
print(ages)

0    25
1    30
Name: Age, dtype: int64

#DataSelection #Indexing #Statistics

---

13. df.loc[]
Access a group of rows and columns by label(s) or a boolean array.

import pandas as pd
data = {'Age': [25, 30, 35], 'City': ['NY', 'LA', 'CH']}
df = pd.DataFrame(data, index=['Alice', 'Bob', 'Charlie'])
print(df.loc['Bob'])

Age     30
City    LA
Name: Bob, dtype: object

---

14. df.iloc[]
Access a group of rows and columns by integer position(s).

import pandas as pd
data = {'Age': [25, 30, 35], 'City': ['NY', 'LA', 'CH']}
df = pd.DataFrame(data, index=['Alice', 'Bob', 'Charlie'])
print(df.iloc[1]) # Get the second row (index 1)

Age     30
City    LA
Name: Bob, dtype: object

---

15. df.isnull()
Returns a DataFrame of the same shape with boolean values indicating if a value is missing (NaN).

import pandas as pd
import numpy as np
df = pd.DataFrame({'A': [1, np.nan], 'B': [3, 4]})
print(df.isnull())

A      B
0  False  False
1   True  False

---

16. df.dropna()
Removes missing values.

import pandas as pd
import numpy as np
df = pd.DataFrame({'A': [1, np.nan, 3], 'B': [4, 5, 6]})
cleaned_df = df.dropna()
print(cleaned_df)

A  B
0  1.0  4
2  3.0  6

#DataCleaning #MissingData

---

17. df.fillna()
Fills missing (NaN) values with a specified value or method.

import pandas as pd
import numpy as np
df = pd.DataFrame({'Score': [90, 85, np.nan, 92]})
filled_df = df.fillna(0)
print(filled_df)

Score
0   90.0
1   85.0
2    0.0
3   92.0

---

18. df.drop_duplicates()
Removes duplicate rows from the DataFrame.

import pandas as pd
data = {'Name': ['Alice', 'Bob', 'Alice'], 'Age': [25, 30, 25]}
df = pd.DataFrame(data)
unique_df = df.drop_duplicates()
print(unique_df)

Name  Age
0  Alice   25
1    Bob   30

---

19. df.rename()
Alters axes labels (e.g., column names).

import pandas as pd
df = pd.DataFrame({'A': [1], 'B': [2]})
renamed_df = df.rename(columns={'A': 'Column_A', 'B': 'Column_B'})
print(renamed_df)

Column_A  Column_B
0         1         2

---

20. series.value_counts()
Returns a Series containing counts of unique values.

import pandas as pd
s = pd.Series(['A', 'B', 'A', 'C', 'A', 'B'])
print(s.value_counts())

A    3
B    2
C    1
dtype: int64

#DataManipulation #Transformation

---

21. series.unique()
Returns an array of unique values in a Series.

import pandas as pd
s = pd.Series(['A', 'B', 'A', 'C', 'A', 'B'])
print(s.unique())

['A' 'B' 'C']

---

22. df.sort_values()
Sorts a DataFrame by the values of one or more columns.

import pandas as pd
data = {'Name': ['Charlie', 'Alice', 'Bob'], 'Age': [35, 25, 30]}
df = pd.DataFrame(data)
sorted_df = df.sort_values(by='Age')
print(sorted_df)

Name  Age
1    Alice   25
2      Bob   30
0  Charlie   35

---

23. df.groupby()
Groups a DataFrame using a mapper or by a Series of columns for aggregation.

import pandas as pd
data = {'Dept': ['HR', 'IT', 'HR', 'IT'], 'Salary': [70, 85, 75, 90]}
df = pd.DataFrame(data)
grouped = df.groupby('Dept').mean()
print(grouped)

Salary
Dept        
HR      72.5
IT      87.5

---

24. df.agg()
Applies one or more aggregations over the specified axis.

import pandas as pd
data = {'Dept': ['HR', 'IT', 'HR', 'IT'], 'Salary': [70, 85, 75, 90]}
df = pd.DataFrame(data)
agg_results = df.groupby('Dept')['Salary'].agg(['mean', 'sum'])
print(agg_results)

mean  sum
Dept            
HR     72.5  145
IT     87.5  175

#Aggregation #Grouping #Sorting

---

25. df.apply()
Applies a function along an axis of the DataFrame.

import pandas as pd
df = pd.DataFrame({'A': [1, 2, 3], 'B': [10, 20, 30]})
# Apply a function to double each value in column 'A'
df['A_doubled'] = df['A'].apply(lambda x: x * 2)
print(df)

A   B  A_doubled
0  1  10          2
1  2  20          4
2  3  30          6

---

26. pd.merge()
Merges two DataFrames based on a common column or index, similar to a SQL join.

import pandas as pd
df1 = pd.DataFrame({'ID': [1, 2], 'Name': ['Alice', 'Bob']})
df2 = pd.DataFrame({'ID': [1, 2], 'Role': ['Engineer', 'Analyst']})
merged_df = pd.merge(df1, df2, on='ID')
print(merged_df)

ID   Name      Role
0   1  Alice  Engineer
1   2    Bob   Analyst

---

27. pd.concat()
Concatenates (stacks) pandas objects along a particular axis.

import pandas as pd
df1 = pd.DataFrame({'A': ['A0'], 'B': ['B0']})
df2 = pd.DataFrame({'A': ['A1'], 'B': ['B1']})
concatenated_df = pd.concat([df1, df2])
print(concatenated_df)

A   B
0  A0  B0
0  A1  B1

---

28. df.pivot_table()
Creates a spreadsheet-style pivot table as a DataFrame.

import pandas as pd
data = {'Date': ['2023-01-01', '2023-01-01', '2023-01-02'],
        'City': ['NY', 'LA', 'NY'],
        'Sales': [100, 150, 120]}
df = pd.DataFrame(data)
pivot = df.pivot_table(values='Sales', index='Date', columns='City')
print(pivot)

City             LA     NY
Date                      
2023-01-01  150.0  100.0
2023-01-02    NaN  120.0

#CombiningData #PivotTable

---

29. df.set_index()
Sets one or more existing columns as the DataFrame index.

import pandas as pd
data = {'ID': ['a1', 'a2'], 'Name': ['Alice', 'Bob']}
df = pd.DataFrame(data)
df_indexed = df.set_index('ID')
print(df_indexed)

Name
ID        
a1   Alice
a2     Bob

---

30. df.reset_index()
Resets the index of the DataFrame, making the old index a new column.

import pandas as pd
data = {'Name': ['Alice', 'Bob']}
df = pd.DataFrame(data, index=['a1', 'a2'])
df_reset = df.reset_index()
print(df_reset)

index   Name
0    a1  Alice
1    a2    Bob

#Indexing #PandasTips

━━━━━━━━━━━━━━━
By: @DataScienceM ✨

📌 The History of Bodybuilding Through Network Visualization

🗂 Category: DATA SCIENCE

🕒 Date: 2024-05-30 | ⏱️ Read time: 5 min read

Constructing the Shared Podium Graph of Mr. Olympia Winners (1965-2023) using Python and Gephi.

Top 30 MATLAB Image Processing Functions

#MATLAB #ImageProcessing #Basics

#1. imread()
Reads an image from a file into a matrix.

img = imread('peppers.png');
disp('Image "peppers.png" loaded into variable "img".');

Image "peppers.png" loaded into variable "img".

#2. imshow()
Displays an image in a figure window.

img = imread('peppers.png');
imshow(img);
title('Peppers Image');

Output: A new figure window opens, displaying the 'peppers.png' image with the title "Peppers Image".

#3. imwrite()
Writes an image matrix to a file.

img = imread('cameraman.tif');
imwrite(img, 'my_cameraman.jpg');
disp('Image saved as my_cameraman.jpg');

Image saved as my_cameraman.jpg

#4. size()
Returns the dimensions of the image matrix (rows, columns, color channels).

rgb_img = imread('peppers.png');
gray_img = imread('cameraman.tif');
size_rgb = size(rgb_img);
size_gray = size(gray_img);
disp(['Size of RGB image: ', num2str(size_rgb)]);
disp(['Size of grayscale image: ', num2str(size_gray)]);

Size of RGB image: 384   512     3
Size of grayscale image: 256   256

#5. rgb2gray()
Converts an RGB color image to a grayscale intensity image.

rgb_img = imread('peppers.png');
gray_img = rgb2gray(rgb_img);
imshow(gray_img);
title('Grayscale Peppers');

Output: A figure window displays the grayscale version of the peppers image.

---
#MATLAB #ImageProcessing #Conversion #Transformation

#6. im2double()
Converts an image to double-precision format, scaling data to the range [0, 1].

img_uint8 = imread('cameraman.tif');
img_double = im2double(img_uint8);
disp(['Max value of original image: ', num2str(max(img_uint8(:)))]);
disp(['Max value of double image: ', num2str(max(img_double(:)))]);

Max value of original image: 253
Max value of double image: 0.99216

#7. imresize()
Resizes an image to a specified size.

img = imread('cameraman.tif');
resized_img = imresize(img, 0.5); % Resize to 50% of original size
imshow(resized_img);
title('Resized Cameraman');

Output: A figure window displays the cameraman image at half its original size.

#8. imrotate()
Rotates an image by a specified angle.

img = imread('cameraman.tif');
rotated_img = imrotate(img, 30, 'bilinear', 'crop');
imshow(rotated_img);
title('Rotated 30 Degrees');

Output: A figure window displays the cameraman image rotated by 30 degrees, cropped to the original size.

#9. imcrop()
Crops an image to a specified rectangle.

img = imread('peppers.png');
% [xmin ymin width height]
cropped_img = imcrop(img, [100 80 250 200]);
imshow(cropped_img);
title('Cropped Image');

Output: A figure window displays only the rectangular section specified from the peppers image.

#10. rgb2hsv()
Converts an RGB image to the Hue-Saturation-Value (HSV) color space.

rgb_img = imread('peppers.png');
hsv_img = rgb2hsv(rgb_img);
hue_channel = hsv_img(:,:,1); % Extract the Hue channel
imshow(hue_channel);
title('Hue Channel of Peppers Image');

Output: A figure window displays the Hue channel of the peppers image as a grayscale image.

---
#MATLAB #ImageProcessing #Enhancement

#11. imhist()
Displays the histogram of an image, showing the distribution of pixel intensity values.

gray_img = imread('pout.tif');
imhist(gray_img);
title('Histogram of a Low-Contrast Image');

Output: A figure window with a bar chart showing the intensity distribution of the 'pout.tif' image.

#12. histeq()
Enhances contrast using histogram equalization.

low_contrast_img = imread('pout.tif');
high_contrast_img = histeq(low_contrast_img);
imshow(high_contrast_img);
title('Histogram Equalized Image');

Output: A figure window displays a higher contrast version of the 'pout.tif' image.

#13. imadjust()
Adjusts image intensity values or colormap by mapping intensity values to new values.

img = imread('cameraman.tif');
adjusted_img = imadjust(img, [0.3 0.7], []);
imshow(adjusted_img);
title('Intensity Adjusted Image');

Output: A figure window showing a high-contrast version of the cameraman image, where intensities between 0.3 and 0.7 are stretched to the full [0, 1] range.

#14. imtranslate()
Translates (shifts) an image horizontally and vertically.

img = imread('cameraman.tif');
translated_img = imtranslate(img, [25, 15]); % Shift 25 pixels right, 15 pixels down
imshow(translated_img);
title('Translated Image');

Output: A figure window shows the cameraman image shifted to the right and down.

#15. imsharpen()
Sharpens an image using the unsharp masking method.

img = imread('peppers.png');
sharpened_img = imsharpen(img);
imshow(sharpened_img);
title('Sharpened Image');

Output: A figure window displays a crisper, more detailed version of the peppers image.

---
#MATLAB #ImageProcessing #Filtering #Noise

#16. imnoise()
Adds a specified type of noise to an image.

img = imread('cameraman.tif');
noisy_img = imnoise(img, 'salt & pepper', 0.02);
imshow(noisy_img);
title('Image with Salt & Pepper Noise');

Output: A figure window displays the cameraman image with random white and black pixels (noise).

#17. fspecial()
Creates a predefined 2-D filter kernel (e.g., for averaging, Gaussian blur, Laplacian).

h = fspecial('motion', 20, 45); % Create a motion blur filter
disp('Generated a 2D motion filter kernel.');
disp(h);

Generated a 2D motion filter kernel.
(Output is a matrix representing the filter kernel)

#18. imfilter()
Filters a multidimensional image with a specified filter kernel.

img = imread('cameraman.tif');
h = fspecial('motion', 20, 45);
motion_blur_img = imfilter(img, h, 'replicate');
imshow(motion_blur_img);
title('Motion Blurred Image');

Output: A figure window shows the cameraman image with a motion blur effect applied at a 45-degree angle.

#19. medfilt2()
Performs 2-D median filtering, which is excellent for removing 'salt & pepper' noise.

noisy_img = imnoise(imread('cameraman.tif'), 'salt & pepper', 0.02);
denoised_img = medfilt2(noisy_img);
imshow(denoised_img);
title('Denoised with Median Filter');

Output: A figure window shows the noisy image significantly cleaned up, with most salt & pepper noise removed.

#20. edge()
Finds edges in an intensity image using various algorithms (e.g., Sobel, Canny).

img = imread('cameraman.tif');
edges = edge(img, 'Canny');
imshow(edges);
title('Edges found with Canny Detector');

Output: A figure window displays a binary image showing only the detected edges from the original image in white.

---
#MATLAB #ImageProcessing #Segmentation #Morphology

#21. graythresh()
Computes a global image threshold from a grayscale image using Otsu's method.

img = imread('coins.png');
level = graythresh(img);
disp(['Optimal threshold level (Otsu): ', num2str(level)]);

Optimal threshold level (Otsu): 0.49412

#22. imbinarize()
Converts a grayscale image to a binary image based on a threshold.

img = imread('coins.png');
level = graythresh(img); % Find optimal threshold
bw_img = imbinarize(img, level);
imshow(bw_img);
title('Binarized Image (Otsu Method)');

Output: A figure window displays a black and white image of the coins.

#23. strel()
Creates a morphological structuring element (SE), which is used to probe an image in morphological operations.

se = strel('disk', 5);
disp('Created a disk-shaped structuring element with radius 5.');
disp(se);

Created a disk-shaped structuring element with radius 5.
(Output describes the strel object and shows its matrix representation)

#24. imdilate()
Dilates a binary image, making objects larger and filling small holes.

img = imread('text.png');
se = strel('line', 3, 90); % A vertical line SE
dilated_img = imdilate(img, se);
imshow(dilated_img);
title('Dilated Text');

Output: A figure window shows the text characters appearing thicker, especially in the vertical direction.

#25. imerode()
Erodes a binary image, shrinking objects and removing small noise.

img = imread('text.png');
se = strel('line', 3, 0); % A horizontal line SE
eroded_img = imerode(img, se);
imshow(eroded_img);
title('Eroded Text');

Output: A figure window shows the text characters appearing thinner, with horizontal parts possibly disappearing.

---
#MATLAB #ImageProcessing #Analysis

#26. imopen()
Performs morphological opening (erosion followed by dilation). It smooths contours and removes small objects.

original = imread('circbw.tif');
se = strel('disk', 10);
opened_img = imopen(original, se);
imshow(opened_img);
title('Morphologically Opened Image');

Output: A figure window displays the image with small protrusions removed and gaps between objects widened.

#27. bwareaopen()
Removes all connected components (objects) from a binary image that have fewer than a specified number of pixels.

img = imread('text.png');
cleaned_img = bwareaopen(img, 50); % Remove objects with fewer than 50 pixels
imshow(cleaned_img);
title('Image after removing small objects');

Output: A figure window shows the text image with small noise specks or broken parts of characters removed.

#28. bwlabel()
Labels connected components in a binary image.

img = imread('text.png');
[L, num] = bwlabel(img);
disp(['Number of connected objects found: ', num2str(num)]);

Number of connected objects found: 114

#29. regionprops()
Measures a set of properties for each labeled region in an image.

img = imread('coins.png');
bw = imbinarize(img);
stats = regionprops('table', bw, 'Centroid', 'MajorAxisLength', 'MinorAxisLength');
disp('Properties of the first 3 coins:');
disp(stats(1:3,:));

Properties of the first 3 coins:
    Centroid       MajorAxisLength    MinorAxisLength
    ________    _______________    _______________

    52.715    26.839              48.24              47.369        
    134.5     27.067              48.745             47.534        
    215.01    29.805              47.854             47.502

#30. hough()
Performs the Hough transform, used to detect lines in an image.

img = imrotate(imread('circuit.tif'), 33, 'crop');
edges = edge(img, 'canny');
[H, T, R] = hough(edges);
imshow(imadjust(rescale(H)), 'XData', T, 'YData', R, ...
      'InitialMagnification', 'fit');
xlabel('\theta'), ylabel('\rho');
title('Hough Transform of Circuit');

Output: A figure window displaying the Hough transform as an image, where bright spots correspond to potential lines in the original image.

━━━━━━━━━━━━━━━
By: @DataScienceM ✨

📌 Constructive Heuristics in Discrete Optimization

🗂 Category:

🕒 Date: 2024-05-30 | ⏱️ Read time: 13 min read

Obtain initial solutions for combinatorial optimization problems with Python examples

Top 100 Data Analyst Interview Questions & Answers

#DataAnalysis #InterviewQuestions #SQL #Python #Statistics #CaseStudy #DataScience

Part 1: SQL Questions (Q1-30)

#1. What is the difference between DELETE, TRUNCATE, and DROP?
A:
• DELETE is a DML command that removes rows from a table based on a WHERE clause. It is slower as it logs each row deletion and can be rolled back.
• TRUNCATE is a DDL command that quickly removes all rows from a table. It is faster, cannot be rolled back, and resets table identity.
• DROP is a DDL command that removes the entire table, including its structure, data, and indexes.

#2. Select all unique departments from the employees table.
A: Use the DISTINCT keyword.

SELECT DISTINCT department
FROM employees;

#3. Find the top 5 highest-paid employees.
A: Use ORDER BY and LIMIT.

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

#4. What is the difference between WHERE and HAVING?
A:
• WHERE is used to filter records before any groupings are made (i.e., it operates on individual rows).
• HAVING is used to filter groups after aggregations (GROUP BY) have been performed.

-- Find departments with more than 10 employees
SELECT department, COUNT(employee_id)
FROM employees
GROUP BY department
HAVING COUNT(employee_id) > 10;

#5. What are the different types of SQL joins?
A:
• (INNER) JOIN: Returns records that have matching values in both tables.
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table.
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table.
• FULL (OUTER) JOIN: Returns all records when there is a match in either the left or right table.
• SELF JOIN: A regular join, but the table is joined with itself.

#6. Write a query to find the second-highest salary.
A: Use OFFSET or a subquery.

-- Method 1: Using OFFSET
SELECT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

-- Method 2: Using a Subquery
SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

#7. Find duplicate emails in a customers table.
A: Group by the email column and use HAVING to find groups with a count greater than 1.

SELECT email, COUNT(email)
FROM customers
GROUP BY email
HAVING COUNT(email) > 1;

#8. What is a primary key vs. a foreign key?
A:
• A Primary Key is a constraint that uniquely identifies each record in a table. It must contain unique values and cannot contain NULL values.
• A Foreign Key is a key used to link two tables together. It is a field (or collection of fields) in one table that refers to the Primary Key in another table.

#9. Explain Window Functions. Give an example.
A: Window functions perform a calculation across a set of table rows that are somehow related to the current row. Unlike aggregate functions, they do not collapse rows.

-- Rank employees by salary within each department
SELECT
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

#10. What is a CTE (Common Table Expression)?
A: A CTE is a temporary, named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It helps improve readability and break down complex queries.

WITH DepartmentSales AS (
    SELECT department, SUM(sale_amount) as total_sales
    FROM sales
    GROUP BY department
)
SELECT department, total_sales
FROM DepartmentSales
WHERE total_sales > 100000;

---
#11. Difference between UNION and UNION ALL?
A:
• UNION combines the result sets of two or more SELECT statements and removes duplicate rows.
• UNION ALL also combines result sets but includes all rows, including duplicates. It is faster because it doesn't check for duplicates.

#12. How would you find the total number of employees in each department?
A: Use COUNT() with GROUP BY.

SELECT department, COUNT(employee_id) as number_of_employees
FROM employees
GROUP BY department;

#13. What is the difference between RANK() and DENSE_RANK()?
A:
• RANK() assigns a rank to each row within a partition. If there are ties, it skips the next rank(s). (e.g., 1, 2, 2, 4)
• DENSE_RANK() also assigns ranks, but it does not skip any ranks in case of ties. (e.g., 1, 2, 2, 3)

#14. Write a query to get the Nth highest salary.
A: Use DENSE_RANK() in a CTE.

WITH SalaryRanks AS (
    SELECT
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) as rnk
    FROM employees
)
SELECT salary
FROM SalaryRanks
WHERE rnk = 5; -- For the 5th highest salary

#15. What is COALESCE() used for?
A: The COALESCE() function returns the first non-NULL value in a list of expressions. It's useful for providing default values for nulls.

SELECT name, COALESCE(commission, 0) as commission
FROM employees; -- Replaces NULL commissions with 0

---
#16. How would you select all employees whose name starts with 'A'?
A: Use the LIKE operator with a wildcard (%).

SELECT name
FROM employees
WHERE name LIKE 'A%';

#17. Get the current date and time.
A: This is function-dependent on the SQL dialect.
• PostgreSQL/MySQL: NOW()
• SQL Server: GETDATE()

SELECT NOW();

#18. How can you extract the month from a date?
A: Use the EXTRACT function or MONTH().

-- Standard SQL
SELECT EXTRACT(MONTH FROM '2023-10-27');
-- MySQL
SELECT MONTH('2023-10-27');

#19. What is a subquery? What are the types?
A: A subquery is a query nested inside another query.
• Scalar Subquery: Returns a single value (one row, one column).
• Multi-row Subquery: Returns multiple rows.
• Correlated Subquery: An inner query that depends on the outer query for its values. It is evaluated once for each row processed by the outer query.

#20. Write a query to find all employees who work in the 'Sales' department.
A: Use a JOIN or a subquery.

-- Using JOIN (preferred)
SELECT e.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.name = 'Sales';

---
#21. How would you calculate the month-over-month growth rate of sales?
A: Use the LAG() window function to get the previous month's sales and then apply the growth formula.

WITH MonthlySales AS (
    SELECT
        DATE_TRUNC('month', order_date)::DATE as sales_month,
        SUM(sale_amount) as total_sales
    FROM sales
    GROUP BY 1
)
SELECT
    sales_month,
    total_sales,
    (total_sales - LAG(total_sales, 1) OVER (ORDER BY sales_month)) / LAG(total_sales, 1) OVER (ORDER BY sales_month) * 100 as growth_rate
FROM MonthlySales;

#22. What is an index in a database? Why is it useful?
A: An index is a special lookup table that the database search engine can use to speed up data retrieval. It works like an index in the back of a book. It improves the speed of SELECT queries but can slow down data modification (INSERT, UPDATE, DELETE).

#23. Difference between VARCHAR and CHAR?
A:
• CHAR is a fixed-length string data type. CHAR(10) will always store 10 characters, padding with spaces if necessary.
• VARCHAR is a variable-length string data type. VARCHAR(10) can store up to 10 characters, but only uses the storage needed for the actual string.

#24. What is a CASE statement?
A: The CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement).

SELECT
    name,
    salary,
    CASE
        WHEN salary > 100000 THEN 'High Earner'
        WHEN salary > 50000 THEN 'Mid Earner'
        ELSE 'Low Earner'
    END as salary_category
FROM employees;

#25. Find the cumulative sum of sales over time.
A: Use a SUM() window function.

SELECT
    order_date,
    sale_amount,
    SUM(sale_amount) OVER (ORDER BY order_date) as cumulative_sales
FROM sales;

---
#26. What does GROUP_CONCAT (MySQL) or STRING_AGG (PostgreSQL) do?
A: These functions concatenate strings from a group into a single string with a specified separator.

-- PostgreSQL example
SELECT department, STRING_AGG(name, ', ') as employee_names
FROM employees
GROUP BY department;

#27. What is data normalization? Why is it important?
A: Data normalization is the process of organizing columns and tables in a relational database to minimize data redundancy. It is important because it reduces storage space, eliminates inconsistent data, and simplifies data management.

#28. Write a query to find users who made a purchase in January but not in February.
A: Use LEFT JOIN or NOT IN.

SELECT user_id
FROM sales
WHERE EXTRACT(MONTH FROM order_date) = 1
EXCEPT
SELECT user_id
FROM sales
WHERE EXTRACT(MONTH FROM order_date) = 2;

#29. What is a self-join?
A: A self-join is a join in which a table is joined to itself. This is useful for querying hierarchical data or comparing rows within the same table.

-- Find employees who have the same manager
SELECT e1.name as employee1, e2.name as employee2, e1.manager_id
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.manager_id AND e1.id <> e2.id;

#30. What is the execution order of a SQL query?
A: The logical processing order is generally:
• FROM / JOIN
• WHERE
• GROUP BY
• HAVING
• SELECT
• DISTINCT
• ORDER BY
• LIMIT / OFFSET

---
Part 2: Python (Pandas/NumPy) Questions (Q31-50)

#31. How do you select a column named 'age' from a pandas DataFrame df?
A: There are two common ways.

# Method 1 (preferred, handles column names with spaces)
age_column = df['age']

# Method 2 (dot notation)
age_column = df.age

#32. How do you filter a DataFrame df to get rows where 'age' is greater than 30?
A: Use boolean indexing.

filtered_df = df[df['age'] > 30]

#33. What's the difference between .loc and .iloc?
A:
• .loc is a label-based indexer. You use row and column names to select data.
• .iloc is an integer-position-based indexer. You use integer indices (like in Python lists) to select data.

# .loc example (select row with index 'a')
df.loc['a']
# .iloc example (select first row)
df.iloc[0]

#34. How do you handle missing values in a DataFrame?
A: Several methods:
• df.isnull().sum(): To count missing values per column.
• df.dropna(): To remove rows/columns with missing values.
• df.fillna(value): To fill missing values with a specific value (e.g., 0, mean, median).

# Fill missing age values with the mean age
mean_age = df['age'].mean()
df['age'].fillna(mean_age, inplace=True)

#35. How would you create a new column 'age_group' based on the 'age' column?
A: Use pd.cut or a custom function with .apply.

bins = [0, 18, 35, 60, 100]
labels = ['Child', 'Young Adult', 'Adult', 'Senior']
df['age_group'] = pd.cut(df['age'], bins=bins, labels=labels, right=False)

#36. How do you merge two DataFrames, df1 and df2, on a common column 'user_id'?
A: Use pd.merge().

merged_df = pd.merge(df1, df2, on='user_id', how='inner') # 'how' can be 'left', 'right', 'outer'

#37. How can you group a DataFrame by 'department' and calculate the average 'salary'?
A: Use .groupby() and .agg() or a direct aggregation function.

avg_salary_by_dept = df.groupby('department')['salary'].mean()

#38. What is the purpose of the .apply() method in pandas?
A: .apply() lets you apply a function along an axis of a DataFrame. It is used for complex, custom operations that are not covered by built-in pandas functions.

# Create a new column by applying a custom function to the 'salary' column
def categorize_salary(salary):
    if salary > 100000:
        return 'High'
    return 'Low'

df['salary_category'] = df['salary'].apply(categorize_salary)

#39. How do you remove duplicate rows from a DataFrame?
A: Use the .drop_duplicates() method.

# Keep the first occurrence of each duplicate row
unique_df = df.drop_duplicates()

# Keep the last occurrence
unique_df_last = df.drop_duplicates(keep='last')

#40. Explain the difference between join() and merge() in pandas.
A:
• merge() is more versatile and is the main entry point for database-style join operations. It can join on columns or indices.
• join() is a convenience method for joining DataFrames primarily on their indices. It can also join on a column of the calling DataFrame to the index of the other.
In most cases, merge() is the more powerful and flexible choice.
---
#41. How do you convert a column's data type, e.g., 'date_string' to datetime?
A: Use pd.to_datetime().

df['date'] = pd.to_datetime(df['date_string'])

#42. What is a pivot table and how do you create one in pandas?
A: A pivot table is a data summarization tool. It reshapes data by aggregating values based on one or more grouping keys along rows and columns. Use df.pivot_table().

# Create a pivot table to see average sales by region and product
pivot = df.pivot_table(values='sales', index='region', columns='product', aggfunc='mean')

#43. How would you select rows with multiple conditions, e.g., 'age' > 30 and 'city' == 'New York'?
A: Use boolean indexing with & for AND, and | for OR. Wrap each condition in parentheses.

filtered_df = df[(df['age'] > 30) & (df['city'] == 'New York')]

#44. How can you find the number of unique values in a column?
A: Use the .nunique() method.

unique_cities_count = df['city'].nunique()

#45. What is the difference between a pandas Series and a DataFrame?
A:
• A Series is a one-dimensional labeled array, capable of holding any data type. It's like a single column in a spreadsheet.
• A DataFrame is a two-dimensional labeled data structure with columns of potentially different types. It's like a whole spreadsheet or an SQL table.

#46. How do you sort a DataFrame by the 'salary' column in descending order?
A: Use .sort_values().

sorted_df = df.sort_values(by='salary', ascending=False)

#47. What is method chaining in pandas?
A: Method chaining is the practice of calling methods on a DataFrame sequentially. It improves code readability by reducing the need for intermediate variables.

# Example of method chaining
result = (df[df['age'] > 30]
          .groupby('department')
          ['salary']
          .mean()
          .sort_values(ascending=False))

#48. How do you rename the column 'user_name' to 'username'?
A: Use the .rename() method.

df.rename(columns={'user_name': 'username'}, inplace=True)

#49. How do you get the correlation matrix for all numerical columns in a DataFrame?
A: Use the .corr() method.

correlation_matrix = df.corr(numeric_only=True)

#50. When would you use NumPy over pandas?
A:
• Use NumPy for performing complex mathematical operations on numerical data, especially in machine learning, where data is often represented as arrays (matrices). It is faster for numerical computations.
• Use pandas when you need to work with tabular data, handle missing values, use labeled axes, and perform data manipulation, cleaning, and preparation tasks. Pandas is built on top of NumPy.

---
Part 3: Statistics & Probability Questions (Q51-65)

#51. What is the difference between mean, median, and mode?
A:
• Mean: The average of all data points. It is sensitive to outliers.
• Median: The middle value of a dataset when sorted. It is robust to outliers.
• Mode: The most frequently occurring value in a dataset.

#52. Explain p-value.
A: The p-value is the probability of observing results as extreme as, or more extreme than, what was actually observed, assuming the null hypothesis is true. A small p-value (typically ≤ 0.05) indicates strong evidence against the null hypothesis, so you reject it.

#53. What are Type I and Type II errors?
A:
• Type I Error (False Positive): Rejecting the null hypothesis when it is actually true. (e.g., concluding a new drug is effective when it is not).
• Type II Error (False Negative): Failing to reject the null hypothesis when it is actually false. (e.g., concluding a new drug is not effective when it actually is).

#54. What is a confidence interval?
A: A confidence interval is a range of values, derived from sample statistics, that is likely to contain the value of an unknown population parameter. For example, a 95% confidence interval means that if we were to repeat the experiment many times, 95% of the calculated intervals would contain the true population parameter.

#55. Explain the Central Limit Theorem (CLT).
A: The Central Limit Theorem states that the sampling distribution of the sample mean will be approximately normally distributed, regardless of the original population's distribution, as long as the sample size is sufficiently large (usually n > 30). This is fundamental to hypothesis testing.

#56. What is the difference between correlation and causation?
A:
• Correlation is a statistical measure that indicates the extent to which two or more variables fluctuate together. A positive correlation indicates that the variables increase or decrease in parallel; a negative correlation indicates that as one variable increases, the other decreases.
• Causation indicates that one event is the result of the occurrence of the other event; i.e., there is a causal relationship between the two events. Correlation does not imply causation.

#57. What is A/B testing?
A: A/B testing is a randomized experiment with two variants, A and B. It is a method of comparing two versions of a webpage, app, or feature against each other to determine which one performs better. A key metric is chosen (e.g., click-through rate), and statistical tests are used to determine if the difference in performance is statistically significant.

#58. What are confounding variables?
A: A confounding variable is an "extra" variable that you didn't account for. It can ruin an experiment and give you useless results because it is related to both the independent and dependent variables, creating a spurious association.

#59. What is selection bias?
A: Selection bias is the bias introduced by the selection of individuals, groups, or data for analysis in such a way that proper randomization is not achieved, thereby ensuring that the sample obtained is not representative of the population intended to be analyzed.

#60. You are rolling two fair six-sided dice. What is the probability of rolling a sum of 7?
A:
• Total possible outcomes: 6 * 6 = 36.
• Favorable outcomes for a sum of 7: (1,6), (2,5), (3,4), (4,3), (5,2), (6,1). There are 6 favorable outcomes.
• Probability = Favorable Outcomes / Total Outcomes = 6 / 36 = 1/6.

---
#61. What is standard deviation and variance?
A:
• Variance measures how far a set of numbers is spread out from their average value. It is the average of the squared differences from the mean.
• Standard Deviation is the square root of the variance. It is expressed in the same units as the data, making it more interpretable than variance. It quantifies the amount of variation or dispersion of a set of data values.

#62. Explain conditional probability.
A: Conditional probability is the probability of an event occurring, given that another event has already occurred. It is denoted as P(A|B), the probability of event A given event B. The formula is P(A|B) = P(A and B) / P(B).

#63. What is the law of large numbers?
A: The law of large numbers is a theorem that states that as the number of trials of a random process increases, the average of the results obtained from a large number of trials should be close to the expected value, and will tend to become closer as more trials are performed.

#64. What is a normal distribution? What are its key properties?
A: A normal distribution, also known as a Gaussian distribution, is a probability distribution that is symmetric about the mean, showing that data near the mean are more frequent in occurrence than data far from the mean.
• Properties: It is bell-shaped, symmetric, and defined by its mean (μ) and standard deviation (σ). About 68% of data falls within one standard deviation of the mean, 95% within two, and 99.7% within three.

#65. What is regression analysis? What are some types?
A: Regression analysis is a set of statistical processes for estimating the relationships between a dependent variable (often called the 'outcome variable') and one or more independent variables (often called 'predictors' or 'features').
• Types: Linear Regression, Logistic Regression, Polynomial Regression, Ridge Regression.

---
Part 4: Product Sense & Case Study Questions (Q66-80)

#66. How would you measure the success of a new feature on Instagram, like 'Reels'?
A: My approach would be:
• Define Goals: What is the feature for? Increased user engagement, attracting new users, competing with TikTok.
• Identify Key Metrics (HEART framework):
- Happiness: User surveys, App Store ratings.
- Engagement: Daily Active Users (DAU) creating/viewing Reels, average view time per user, shares, likes, comments.
- Adoption: Number of new users trying the feature, percentage of DAU using Reels.
- Retention: Does using Reels make users more likely to return to the app? (Cohort analysis).
- Task Success: How easy is it to create a Reel? (Time to create, usage of editing tools).
• Counter Metrics: We must also check for negative impacts, like a decrease in time spent on the main feed or Stories.

#67. The number of daily active users (DAU) for our product dropped by 10% yesterday. How would you investigate?
A: I'd follow a structured process:
• Clarify & Validate: Is the data correct? Is it a tracking error or a real drop? Check dashboards and data pipelines. Is the drop global or regional? Affecting all platforms (iOS, Android, Web)?
• Internal Factors (Our Fault):
- Did we just release a new version? Check for bugs.
- Did we have a server outage? Check system health dashboards.
- Did a marketing campaign just end?
• External Factors (Not Our Fault):
- Was it a major holiday?
- Was there a major news event or competitor launch?
- Is it part of a weekly/seasonal trend? (Compare to last week/year).
• Segment the Data: If it's a real drop, I'd segment by user demographics (new vs. returning users, country, device type) to isolate the source of the drop.

#68. How would you design an A/B test for changing a button color from blue to green on our website's homepage?
A:
• Hypothesis: Changing the button color from blue to green will increase the click-through rate (CTR) because green is often associated with "go" and stands out more.
• Control (A): The current blue button.
• Variant (B): The new green button.
• Key Metric: The primary metric is Click-Through Rate (CTR) = (Number of clicks / Number of impressions).
• Setup: Randomly assign users into two groups. 50% see the blue button (control), 50% see the green button (variant).
• Duration & Sample Size: Calculate the required sample size based on the current CTR and the desired minimum detectable effect. Run the test long enough (e.g., 1-2 weeks) to account for weekly variations.
• Conclusion: After the test, use a statistical test (like a Chi-Squared test) to check if the difference in CTR is statistically significant (p-value < 0.05).

#69. What metrics would you use to evaluate a subscription-based product like Netflix?
A:
• Acquisition: Customer Acquisition Cost (CAC), Sign-ups.
• Engagement: Daily/Monthly Active Users (DAU/MAU), Average viewing hours per user, Content diversity (number of different shows watched).
• Retention: Churn Rate (monthly/annually), Customer Lifetime Value (CLV).
• Monetization: Monthly Recurring Revenue (MRR), Average Revenue Per User (ARPU).