highest-paying data science companies<\/a> and research their interview process to prepare well for the selection process.<\/p>\n\n\n\nQ17. Write a SQL query to find the top 3 departments with the highest average salary, but only include departments with at least 5 employees.<\/h3>\n\n\n\n
Sample Answer: <\/strong>To find the top 3 departments with the highest average salary, while including only those departments that have at least 5 employees, we will follow these steps:<\/p>\n\n\n\n\n- Group the data by department to calculate the average salary and count of employees.<\/li>\n\n\n\n
- Filter out departments with fewer than 5 employees using the HAVING clause.<\/li>\n\n\n\n
- Rank the departments based on their average salary.<\/li>\n\n\n\n
- Retrieve the top 3 departments with the highest average salary.<\/li>\n<\/ul>\n\n\n\n
Here\u2019s how the SQL query looks:<\/p>\n\n\n\n
WITH dept_stats AS (\r\n SELECT \r\n d.department_id,\r\n d.department_name,\r\n COUNT(e.employee_id) AS emp_count,\r\n AVG(e.salary) AS avg_salary\r\n FROM employees e\r\n JOIN departments d ON e.department_id = d.department_id\r\n GROUP BY d.department_id, d.department_name\r\n HAVING COUNT(e.employee_id) >= 5\r\n),\r\nranked_depts AS (\r\n SELECT \r\n department_name,\r\n emp_count,\r\n avg_salary,\r\n RANK() OVER (ORDER BY avg_salary DESC) AS salary_rank\r\n FROM dept_stats\r\n)\r\nSELECT \r\n department_name,\r\n emp_count,\r\n ROUND(avg_salary, 2) AS average_salary\r\nFROM ranked_depts\r\nWHERE salary_rank <= 3\r\nORDER BY avg_salary DESC;\r<\/code><\/pre>\n\n\n\nThis query ranks the departments with at least 5 employees and outputs the top 3 based on average salary.<\/p>\n\n\n\n
Q18. Create a custom iterator class that generates Fibonacci numbers up to a specified limit.<\/h3>\n\n\n\n
Sample Answer: <\/strong>In Python, custom iterators need to implement __iter__ and __next__ methods. The iterator should maintain its state and know when to stop. Here\u2019s the code for creating a custom iterator class that generates Fibonacci numbers up to a specified limit:<\/p>\n\n\n\nclass FibonacciIterator:\r\n def __init__(self, limit):\r\n self.limit = limit\r\n self.previous = 0\r\n self.current = 1\r\n \r\n def __iter__(self):\r\n return self\r\n \r\n def __next__(self):\r\n if self.previous > self.limit:\r\n raise StopIteration\r\n \r\n result = self.previous\r\n self.previous, self.current = (\r\n self.current,\r\n self.previous + self.current\r\n )\r\n return result\r\n\r\n# Example usage\r\nfib = FibonacciIterator(100)\r\nprint(list(fib)) # [0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89]\r<\/code><\/pre>\n\n\n\nQ19. Write a decorator that measures and logs the execution time of any function it decorates.<\/h3>\n\n\n\n
Sample Answer: <\/strong>In Python, decorators allow us to modify or enhance the behavior of functions or methods. The decorator measures and logs the execution time of any function it decorates, which is useful for performance monitoring. The following example uses Python’s time module.<\/p>\n\n\n\nimport time\r\nfrom functools import wraps\r\n\r\ndef measure_time(func):\r\n \"\"\"\r\n Decorator to measure the execution time of a function.\r\n\r\n Parameters:\r\n - func: The function to be wrapped.\r\n\r\n Returns:\r\n - wrapper: The wrapped function that logs execution time.\r\n \"\"\"\r\n @wraps(func)\r\n def wrapper(*args, **kwargs):\r\n start_time = time.time() # Record the start time\r\n result = func(*args, **kwargs) # Call the original function\r\n end_time = time.time() # Record the end time\r\n execution_time = end_time - start_time # Calculate execution time\r\n print(f\"{func.__name__} took {execution_time:.4f} seconds\") # Log execution time\r\n return result # Return the result of the original function\r\n return wrapper\r\n\r\n# Example usage\r\n@measure_time\r\ndef slow_function():\r\n \"\"\"A sample function that simulates a delay.\"\"\"\r\n time.sleep(1) # Simulate a slow operation\r\n return \"Done\"\r\n\r\n# Call the decorated function\r\nresult = slow_function()\r\nprint(result) # Output the result\r<\/code><\/pre>\n\n\n\n\n
Q20. Write a function that resamples daily data to monthly averages and handles missing values appropriately.<\/h3>\n\n\n\n
Sample Answer: <\/strong>To analyze time series data effectively, resampling daily data to monthly averages can help identify trends and patterns. The code will utilize the Pandas library’s \u2018datetime\u2019 functionality and the resample() method, ensuring that missing values are handled appropriately during the calculations.<\/p>\n\n\n\nimport pandas as pd\r\nimport numpy as np\r\n\r\ndef resample_to_monthly(df, date_column, value_column):\r\n \"\"\"\r\n Resample daily data to monthly averages, handling missing values appropriately.\r\n\r\n Parameters:\r\n - df: DataFrame containing the daily data.\r\n - date_column: The name of the column that contains date values.\r\n - value_column: The name of the column that contains the values to be averaged.\r\n\r\n Returns:\r\n - DataFrame containing monthly averages, counts, minimum, and maximum values.\r\n \"\"\"\r\n # Ensure the date column is in datetime format\r\n df[date_column] = pd.to_datetime(df[date_column])\r\n \r\n # Set the date column as the index for resampling\r\n df_indexed = df.set_index(date_column)\r\n \r\n # Resample the data to monthly frequency and calculate statistics\r\n monthly_stats = df_indexed[value_column].resample('M').agg(\r\n average='mean', # Calculate average for the month\r\n count='count', # Count non-null values\r\n min='min', # Minimum value for the month\r\n max='max' # Maximum value for the month\r\n )\r\n \r\n # Reset index to return date as a column\r\n monthly_stats = monthly_stats.reset_index()\r\n \r\n return monthly_stats\r\n\r\n# Example usage\r\ndates = pd.date_range('2023-01-01', '2023-12-31', freq='D')\r\nvalues = np.random.normal(100, 10, len(dates))\r\n\r\n# Introduce some random missing values\r\nmask = np.random.choice([True, False], size=values.shape, p=[0.1, 0.9]) # 10% missing values\r\nvalues[mask] = np.nan\r\n\r\ndf = pd.DataFrame({'date': dates, 'value': values})\r\n\r\n# Resample the daily data to monthly averages\r\nmonthly_data = resample_to_monthly(df, 'date', 'value')\r\n\r\n# Display the resulting monthly statistics\r\nprint(monthly_data)\r<\/code><\/pre>\n<\/div><\/div>\n\n\n\nQ21. Create a function that generates interaction features between numerical columns and creates dummy variables for categorical columns.<\/h3>\n\n\n\n
Sample Answer: <\/strong>Here\u2019s a function that creates interaction features between numerical columns and generates dummy variables for categorical columns:<\/p>\n\n\n\ndef engineer_features(df, numerical_cols, categorical_cols):\r\n \"\"\"\r\n Generate interaction features and dummy variables\r\n \"\"\"\r\n result_df = df.copy()\r\n \r\n # Create interaction features\r\n if len(numerical_cols) >= 2:\r\n for i in range(len(numerical_cols)):\r\n for j in range(i+1, len(numerical_cols)):\r\n col1, col2 = numerical_cols[i], numerical_cols[j]\r\n result_df[f'{col1}_{col2}_interaction'] = (\r\n result_df[col1] * result_df[col2]\r\n )\r\n \r\n # Create dummy variables\r\n for col in categorical_cols:\r\n dummies = pd.get_dummies(\r\n result_df[col], \r\n prefix=col, \r\n drop_first=True\r\n )\r\n result_df = pd.concat([result_df, dummies], axis=1)\r\n result_df.drop(col, axis=1, inplace=True)\r\n \r\n return result_df\r\n\r\n# Example usage\r\ndata = {\r\n 'age': [25, 30, 35],\r\n 'income': [50000, 60000, 70000],\r\n 'education': ['HS', 'BS', 'MS'],\r\n 'location': ['urban', 'rural', 'urban']\r\n}\r\ndf = pd.DataFrame(data)\r\nengineered_df = engineer_features(\r\n df, \r\n numerical_cols=['age', 'income'],\r\n categorical_cols=['education', 'location']\r\n)\r<\/code><\/pre>\n\n\n\nQ22. Implement a function to analyze A\/B test results, including calculating p-values and confidence intervals for the conversion rates difference.<\/h3>\n\n\n\n
Sample Answer: <\/strong>Analyzing A\/B test results is crucial for understanding the impact of changes on conversion rates. Here\u2019s a code that calculates conversion rates, performs a z-test for proportions, and computes confidence intervals for the difference in conversion rates.<\/p>\n\n\n\nimport numpy as np\r\nimport scipy.stats as stats\r\n\r\ndef analyze_ab_test(control_conversions, control_size,\r\n treatment_conversions, treatment_size,\r\n confidence_level=0.95):\r\n \"\"\"\r\n Analyze A\/B test results, including conversion rates, p-values, and confidence intervals.\r\n \r\n Parameters:\r\n - control_conversions: Number of conversions in the control group.\r\n - control_size: Total size of the control group.\r\n - treatment_conversions: Number of conversions in the treatment group.\r\n - treatment_size: Total size of the treatment group.\r\n - confidence_level: Confidence level for the confidence interval.\r\n \r\n Returns:\r\n - Dictionary containing conversion rates, rate difference, p-value, and confidence interval.\r\n \"\"\"\r\n # Calculate conversion rates\r\n control_rate = control_conversions \/ control_size\r\n treatment_rate = treatment_conversions \/ treatment_size\r\n \r\n # Calculate standard errors\r\n control_se = np.sqrt(control_rate * (1 - control_rate) \/ control_size)\r\n treatment_se = np.sqrt(treatment_rate * (1 - treatment_rate) \/ treatment_size)\r\n \r\n # Calculate difference and combined standard error\r\n rate_diff = treatment_rate - control_rate\r\n combined_se = np.sqrt(control_se**2 + treatment_se**2)\r\n \r\n # Calculate z-score and p-value for the difference in conversion rates\r\n z_score = rate_diff \/ combined_se\r\n p_value = 2 * (1 - stats.norm.cdf(abs(z_score))) # Two-tailed p-value\r\n \r\n # Calculate critical z-value for the confidence interval\r\n z_critical = stats.norm.ppf((1 + confidence_level) \/ 2)\r\n ci_lower = rate_diff - z_critical * combined_se\r\n ci_upper = rate_diff + z_critical * combined_se\r\n \r\n return {\r\n 'control_rate': control_rate,\r\n 'treatment_rate': treatment_rate,\r\n 'rate_difference': rate_diff,\r\n 'p_value': p_value,\r\n 'confidence_interval': (ci_lower, ci_upper)\r\n }\r\n\r\n# Example usage\r\nresults = analyze_ab_test(\r\n control_conversions=100,\r\n control_size=1000,\r\n treatment_conversions=120,\r\n treatment_size=1000\r\n)\r\n\r\n# Display results\r\nprint(\"Control Conversion Rate:\", results['control_rate'])\r\nprint(\"Treatment Conversion Rate:\", results['treatment_rate'])\r\nprint(\"Rate Difference:\", results['rate_difference'])\r\nprint(\"P-Value:\", results['p_value'])\r\nprint(\"Confidence Interval:\", results['confidence_interval'])\r<\/code><\/pre>\n\n\n\nQ23. Implement a function that performs time series forecasting using SARIMA (Seasonal ARIMA) and evaluates the model’s performance.<\/h3>\n\n\n\n
Sample Answer: <\/strong>The Seasonal ARIMA (SARIMA) model is effective for time series data exhibiting seasonality. This function fits a SARIMA model to the data, generates forecasts, and evaluates model performance using error metrics and model quality indicators.<\/p>\n\n\n\nHere\u2019s the code for the same:<\/p>\n\n\n\n
from statsmodels.tsa.statespace.sarimax import SARIMAX\r\nfrom sklearn.metrics import mean_absolute_error, mean_squared_error\r\n\r\ndef sarima_forecast(data, order=(1,1,1), seasonal_order=(1,1,1,12)):\r\n \"\"\"\r\n Fit SARIMA model and generate forecasts\r\n \"\"\"\r\n # Fit model\r\n model = SARIMAX(\r\n data,\r\n order=order,\r\n seasonal_order=seasonal_order,\r\n enforce_stationarity=False\r\n )\r\n results = model.fit()\r\n \r\n # Generate forecasts\r\n forecast = results.get_forecast(steps=12)\r\n forecast_mean = forecast.predicted_mean\r\n forecast_ci = forecast.conf_int()\r\n \r\n # Calculate metrics\r\n predictions = results.get_prediction(start=len(data)-12)\r\n pred_mean = predictions.predicted_mean\r\n \r\n metrics = {\r\n 'mae': mean_absolute_error(data[-12:], pred_mean[-12:]),\r\n 'rmse': np.sqrt(mean_squared_error(data[-12:], pred_mean[-12:])),\r\n 'aic': results.aic\r\n }\r\n \r\n return {\r\n 'model': results,\r\n 'forecast': forecast_mean,\r\n 'confidence_intervals': forecast_ci,\r\n 'metrics': metrics\r\n }\r\n\r\n# Example usage\r\nimport numpy as np\r\nnp.random.seed(42)\r\ndates = pd.date_range(start='2020-01-01', end='2023-12-31', freq='M')\r\ndata = pd.Series(np.random.normal(0, 1, len(dates)), index=dates)\r\nforecast_results = sarima_forecast(data)\r<\/code><\/pre>\n\n\n\nQ24. Create a function that performs feature selection using mutual information.<\/h3>\n\n\n\n
Sample Answer: <\/strong>Mutual information is an effective method for feature selection because it measures the statistical dependence between each feature and the target, capturing both linear and non-linear relationships. This function uses mutual information to rank and select the most important features and generates a bar plot of their importance scores. <\/p>\n\n\n\nHere\u2019s how you can create a function that performs feature selection using mutual information:<\/p>\n\n\n\n
from sklearn.feature_selection import mutual_info_regression\r\nimport matplotlib.pyplot as plt\r\n\r\ndef select_features_mutual_info(X, y, n_features=5):\r\n \"\"\"\r\n Select features using mutual information\r\n \"\"\"\r\n # Calculate mutual information scores\r\n mi_scores = mutual_info_regression(X, y)\r\n \r\n # Create DataFrame with scores\r\n feature_scores = pd.DataFrame({\r\n 'feature': X.columns,\r\n 'mi_score': mi_scores\r\n })\r\n \r\n # Sort features by importance\r\n feature_scores = feature_scores.sort_values('mi_score', ascending=False)\r\n \r\n # Plot feature importance\r\n plt.figure(figsize=(10, 6))\r\n plt.bar(range(len(mi_scores)), feature_scores['mi_score'])\r\n plt.xticks(range(len(mi_scores)), feature_scores['feature'], rotation=45)\r\n plt.title('Feature Importance (Mutual Information)')\r\n plt.xlabel('Features')\r\n plt.ylabel('Mutual Information Score')\r\n plt.tight_layout()\r\n \r\n # Select top features\r\n selected_features = feature_scores.head(n_features)['feature'].tolist()\r\n \r\n return {\r\n 'selected_features': selected_features,\r\n 'feature_scores': feature_scores,\r\n 'plot': plt.gcf()\r\n }\r\n\r\n# Example usage\r\nfrom sklearn.datasets import make_regression\r\nX, y = make_regression(n_samples=100, n_features=10, random_state=42)\r\nX_df = pd.DataFrame(X, columns=[f'feature_{i}' for i in range(X.shape[1])])\r\nresults = select_features_mutual_info(X_df, y)\r<\/code><\/pre>\n\n\n\n