import pandas as pd
import numpy as np
import sqlite3
import warnings
warnings.filterwarnings('ignore')
np.random.seed(42)

n_transactions = 1000
n_customers = 200
df = pd.DataFrame({
    'transaction_id': range(1, n_transactions + 1),
    'customer_id': np.random.choice([f'CUST_{i:04d}' for i in range(1, n_customers + 1)], n_transactions),
    'amount': np.round(np.random.normal(150, 60, n_transactions), 2),
    'transaction_type': np.random.choice(['Debit', 'Credit'], n_transactions),
    'channel': np.random.choice(['ATM', 'Online', 'Branch', 'POS'], n_transactions),
    'date': pd.date_range(start='2024-01-01', periods=n_transactions, freq='h')
})
print(df.shape)
print(df.head(3))

customer_ids = [f'CUST_{i:04d}' for i in range(1, 201)]
customers = pd.DataFrame({
    'customer_id': customer_ids,
    'segment': ['Retail'] * 150 + ['Business'] * 50,
    'region': ['Metro'] * 100 + ['Regional'] * 100
})
print(customers.shape)
print(customers.head(3))

accounts = pd.DataFrame({
    'account_id': [f'ACC_{i:05d}' for i in range(1, 201)],
    'customer_id': customer_ids,
    'account_type': np.random.choice(['Savings', 'Cheque', 'Credit'], size=200),
    'open_date': pd.date_range(start='2015-01-01', periods=200, freq='30D')
})
print(accounts.shape)
print(accounts.head(3))

txn_counts = df.groupby('customer_id').size().reset_index(name='txn_count')
print(txn_counts.head())

conn = sqlite3.connect(':memory:')
df.to_sql('transactions', conn, index=False)
sql_query = '''
SELECT customer_id, COUNT(*) AS txn_count
FROM transactions
GROUP BY customer_id
LIMIT 5
'''
sql_txn_counts = pd.read_sql_query(sql_query, conn)
print(sql_txn_counts)

large_txn_customers = df[df['amount'] > 200]['customer_id'].unique()
print('Unique customers with transactions > $200:', len(large_txn_customers))

sql_query = '''
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM transactions
WHERE amount > 200
'''
sql_large_count = pd.read_sql_query(sql_query, conn)
print(sql_large_count)

df_merged = df.merge(customers, on='customer_id', how='left')
print(df_merged[['customer_id','segment','amount']].head(3))

customers.to_sql('customers', conn, index=False)
sql_query = '''
SELECT t.*, c.segment
FROM transactions t
LEFT JOIN customers c ON t.customer_id = c.customer_id
LIMIT 3
'''
sql_join_result = pd.read_sql_query(sql_query, conn)
print(sql_join_result[['customer_id','segment','amount']])

df['month'] = df['date'].dt.to_period('M')
chan_month = df.groupby(['month','channel'])['amount'].sum().reset_index()
print(chan_month.head())

sql_query = '''
SELECT strftime('%Y-%m', date) AS month, channel, SUM(amount) AS total_amount
FROM transactions
GROUP BY month, channel
LIMIT 5
'''
sql_chan_month = pd.read_sql_query(sql_query, conn)
print(sql_chan_month)

df_merged = df.merge(customers, on='customer_id', how='left')
debit_averages = df_merged[df_merged['transaction_type']=='Debit'].groupby('segment')['amount'].mean().reset_index()
print(debit_averages)

sql_query = '''
SELECT c.segment, AVG(t.amount) AS avg_debit
FROM transactions t
LEFT JOIN customers c ON t.customer_id = c.customer_id
WHERE t.transaction_type = 'Debit'
GROUP BY c.segment
'''
sql_debits = pd.read_sql_query(sql_query, conn)
print(sql_debits)

latest_date = df['date'].max()
cutoff = latest_date - pd.Timedelta(days=30)
active_last_month = set(df[df['date'] > cutoff]['customer_id'])
inactive_customers = set(customers['customer_id']) - active_last_month
print('Number of inactive customers in last 30 days:', len(inactive_customers))

inactive_df = pd.DataFrame({'customer_id': list(inactive_customers)})
inactive_df.to_csv('inactive_customers.csv', index=False)
print('Example inactive customers:', inactive_df.head())

pivot = df_merged.pivot_table(index='segment', columns='channel', values='transaction_id', aggfunc='count', fill_value=0)
print(pivot)

sql_query = '''
SELECT c.segment, t.channel, COUNT(*) as txn_count
FROM transactions t
LEFT JOIN customers c ON t.customer_id = c.customer_id
GROUP BY c.segment, t.channel
'''
sql_cross = pd.read_sql_query(sql_query, conn)
print(sql_cross.pivot(index='segment', columns='channel', values='txn_count'))

debit_counts = df[df['transaction_type']=='Debit'].groupby('customer_id').size()
active_customers = debit_counts[debit_counts > 5].index
credit_volumes = df[(df['customer_id'].isin(active_customers)) & (df['transaction_type']=='Credit')]
result = credit_volumes.groupby('customer_id')['amount'].sum().reset_index()
print(result.head())

sql_query = '''
SELECT t1.customer_id, SUM(t1.amount) AS credit_volume
FROM transactions t1
WHERE t1.transaction_type = 'Credit'
AND t1.customer_id IN (
  SELECT customer_id
  FROM transactions
  WHERE transaction_type = 'Debit'
  GROUP BY customer_id
  HAVING COUNT(*) > 5
)
GROUP BY t1.customer_id
LIMIT 5
'''
sql_credit_vols = pd.read_sql_query(sql_query, conn)
print(sql_credit_vols)

# Example mistake: Referencing a wrong column name in pandas
try:
    test = df.groupby('customerid').size()
except Exception as e:
    print('Error:', e)

# Example mistake: Date format mismatch in SQL
try:
    sql_query = '''
    SELECT * FROM transactions WHERE date > '2024/01/01' '''
    bad_dates = pd.read_sql_query(sql_query, conn)
except Exception as e:
    print('Error:', e)

# Check for duplicates
duplicates = df.duplicated().sum()
print('Number of duplicated rows:', duplicates)

daily_vol = df_merged.groupby(['date', 'segment'])['amount'].sum().reset_index()
daily_vol.to_csv('daily_transaction_report.csv', index=False)
print('Report rows:', daily_vol.shape[0])
print(daily_vol.head(3))