Spaces:
Build error
Build error
File size: 4,107 Bytes
402fe18 d6b06b4 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 | import pandas as pd
import numpy as np
from datetime import datetime, timedelta
import random
from sklearn.datasets import make_classification, make_regression
from sklearn.preprocessing import MinMaxScaler
def generate_sales_data(n_rows=1000):
"""Generate synthetic sales data using scikit-learn."""
# Generate synthetic features using make_classification for categorical-like data
X_cat, _ = make_classification(n_samples=n_rows, n_features=2, n_informative=2,
n_redundant=0, n_classes=5, random_state=42)
# Generate synthetic features using make_regression for numerical data
X_num, _ = make_regression(n_samples=n_rows, n_features=2, random_state=42)
# Scale numerical features to reasonable ranges
scaler = MinMaxScaler()
X_num_scaled = scaler.fit_transform(X_num)
# Generate dates
start_date = datetime(2022, 1, 1)
dates = [start_date + timedelta(days=i % 365) for i in range(n_rows)]
dates = [d.strftime('%Y-%m-%d') for d in dates]
# Generate IDs
order_ids = [f'ORD-{i+1:05d}' for i in range(n_rows)]
customer_ids = [f'CUST-{random.randint(1000, 9999)}' for _ in range(n_rows)]
product_ids = [f'PROD-{random.randint(100, 999)}' for _ in range(n_rows)]
# Create the dataframe
sales_df = pd.DataFrame({
'OrderID': order_ids,
'Date': dates,
'CustomerID': customer_ids,
'ProductID': product_ids,
'Quantity': np.round(X_cat[:, 0] * 5 + 5).astype(int), # Scale to 1-10 range
'Price': np.round(X_num_scaled[:, 0] * 990 + 10, 2), # Scale to 10-1000 range
'Cost': np.round(X_num_scaled[:, 1] * 500 + 5, 2) # Scale to 5-505 range
})
# Add some realistic data quality issues
# 1. Missing values
mask = np.random.random(n_rows) < 0.05 # 5% missing rate
sales_df.loc[mask, 'Price'] = np.nan
mask = np.random.random(n_rows) < 0.07 # 7% missing rate
sales_df.loc[mask, 'Cost'] = np.nan
# 2. Duplicate orders (10%)
n_duplicates = int(n_rows * 0.1)
duplicate_indices = np.random.choice(n_rows, n_duplicates, replace=False)
original_indices = np.random.choice(n_rows, n_duplicates, replace=False)
sales_df.iloc[duplicate_indices] = sales_df.iloc[original_indices]
# 3. Inconsistent case in ProductIDs (10%)
mask = np.random.random(n_rows) < 0.1
sales_df.loc[mask, 'ProductID'] = sales_df.loc[mask, 'ProductID'].str.lower()
return sales_df
def generate_sql_query():
"""Generate a sample inefficient SQL query that could be optimized."""
return """
WITH customer_stats AS (
SELECT
customer_id,
COUNT(*) as order_count,
SUM(total_amount) as total_spent,
MAX(order_date) as latest_order_date,
AVG(items_per_order) as avg_items
FROM orders o
JOIN (
SELECT order_id, COUNT(*) as items_per_order
FROM order_items
GROUP BY order_id
) oi ON o.order_id = oi.order_id
WHERE order_date >= '2022-01-01'
GROUP BY customer_id
)
SELECT
c.customer_name,
c.customer_id,
cs.total_spent,
cs.order_count,
cs.latest_order_date,
cs.avg_items as avg_items_per_order
FROM customers c
JOIN customer_stats cs ON c.customer_id = cs.customer_id
JOIN customer_types ct ON c.customer_type = ct.type_id
LEFT JOIN returns r ON c.customer_id = r.customer_id
WHERE
ct.type_name = 'Premium'
AND NOT EXISTS (
SELECT 1
FROM blacklist b
WHERE b.customer_id = c.customer_id
)
AND cs.order_count > 5
ORDER BY cs.total_spent DESC;
"""
if __name__ == "__main__":
# Generate and save sample data
sales_df = generate_sales_data()
sales_df.to_csv('sample_sales_data.csv', index=False)
print("Sample data generated and saved to 'sample_sales_data.csv'")
print("Sample SQL query is available via the generate_sql_query() function") |