BigQuery

# Install the BigQuery client library for Python authentication
!pip install google-cloud-bigquery pandas-gbq

# Import required libraries for BigQuery operations
from google.cloud import bigquery
import pandas as pd

# Create BigQuery client - automatically finds your credentials
client = bigquery.Client()

# Set your project ID (replace with your actual project)
project_id = "flipkart-analytics-prod"

# Write SQL query to analyze revenue by city
query = """
SELECT city, 
       SUM(revenue) as total_revenue,
       COUNT(*) as total_orders
FROM `flipkart-analytics.ecommerce.transactions` 
WHERE date >= '2023-01-01'
GROUP BY city
ORDER BY total_revenue DESC
"""

# Execute query and convert results to pandas DataFrame
df = client.query(query).to_dataframe()

# Display first few rows to verify data
print(df.head())
print(f"\nTotal cities analyzed: {len(df)}")

# Complex window function query for time series analysis
advanced_query = """
SELECT date,
       SUM(revenue) as daily_revenue,
       AVG(rating) as avg_rating,
       SUM(SUM(revenue)) OVER (ORDER BY date) as running_total
FROM `flipkart-analytics.ecommerce.transactions` 
WHERE date BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY date
ORDER BY date
"""

# Execute advanced query and examine time series patterns
time_series_df = client.query(advanced_query).to_dataframe()

# Convert date string to datetime for better plotting
time_series_df['date'] = pd.to_datetime(time_series_df['date'])

# Show sample of running totals
print(time_series_df[['date', 'daily_revenue', 'running_total']].head(7))

# Bad query - scans entire table for SELECT *
expensive_query = """
SELECT * 
FROM `paytm-analytics.payments.transactions`
WHERE amount > 1000
"""

# Check how much data this would scan
job_config = bigquery.QueryJobConfig(dry_run=True)
job = client.query(expensive_query, job_config=job_config)
print(f"This query will scan: {job.total_bytes_processed / 1e9:.2f} GB")

# Optimized query - only select needed columns
optimized_query = """
SELECT transaction_id, date, amount, customer_id
FROM `paytm-analytics.payments.transactions`
WHERE amount > 1000
"""

# Check optimized scan size
optimized_job = client.query(optimized_query, job_config=job_config)
print(f"Optimized query scans: {optimized_job.total_bytes_processed / 1e9:.2f} GB")
savings = (job.total_bytes_processed - optimized_job.total_bytes_processed) / job.total_bytes_processed * 100
print(f"Cost savings: {savings:.1f}%")