DA @dataanalystinterview
Showing posts older than #1933 · Back to latest
SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
SELECT * FROM A INNER JOIN B ON A.id = B.id;
SELECT * FROM A LEFT JOIN B ON A.id = B.id;
SELECT name, department, salary,
RANK() OVER (ORDER BY salary DESC) AS rank_salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_salary
FROM employees;
SELECT MAX(salary) AS second_highest
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);
SELECT product_id, SUM(revenue) AS total_revenue
FROM sales
GROUP BY product_id
ORDER BY total_revenue DESC
LIMIT 3;
SELECT c.customer_name, o.order_date, o.amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;
import numpy as np
def remove_outliers(data):
Q1 = np.percentile(data, 25)
Q3 = np.percentile(data, 75)
IQR = Q3 - Q1
lower = Q1 - 1.5 * IQR
upper = Q3 + 1.5 * IQR
return [x for x in data if lower <= x <= upper]
nested = [[1, 2], [3, 4]]
flat = [item for sublist in nested for item in sublist]
# Or: import itertools; list(itertools.chain.from_iterable(nested))
import pandas as pd
df = pd.read_csv('file.csv')
null_counts = df.isnull().sum(axis=1)
print(null_counts[null_counts > 0].count()) # Rows with at least one null
df.fillna(value) for imputation (e.g., mean), df.dropna() to drop rows/cols, or df.interpolate() for time series. Check with df.isnull().sum() first.loc[] uses labels (e.g., df.loc['row_label']) for selection; iloc[] uses integer positions (e.g., df.iloc[0:2])—great for slicing by index.