run-sql

%%calliope run-sql <datasource> [options]
Your SQL query here

%%calliope run-sql sales_db
SELECT region, SUM(amount) as total
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY region
ORDER BY total DESC

%%calliope run-sql analytics_db --generate-chart
SELECT DATE(created_at) as date, COUNT(*) as signups
FROM users
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE(created_at)
ORDER BY date

result = %calliope run-sql mydb --return-data SELECT * FROM customers LIMIT 100

import pandas as pd
result = %calliope run-sql mydb --return-data SELECT * FROM orders
df = pd.DataFrame(result['data'])
df.describe()

%%calliope run-sql analytics_db
CREATE TEMP TABLE recent_users AS
SELECT * FROM users WHERE created_at > '2024-01-01';

SELECT COUNT(*) as count,
       DATE_TRUNC('month', created_at) as month
FROM recent_users
GROUP BY month
ORDER BY month;

%%calliope run-sql ecommerce_db
SELECT
    c.name as customer,
    COUNT(o.id) as order_count,
    SUM(o.total) as total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.created_at >= '2024-01-01'
GROUP BY c.id, c.name
HAVING COUNT(o.id) > 5
ORDER BY total_spent DESC
LIMIT 20

%%calliope run-sql sales_db --generate-chart
SELECT
    date,
    revenue,
    AVG(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_avg_7d
FROM daily_revenue
WHERE date >= '2024-01-01'
ORDER BY date

%%calliope run-sql analytics_db
WITH monthly_stats AS (
    SELECT
        DATE_TRUNC('month', created_at) as month,
        COUNT(*) as users,
        COUNT(*) FILTER (WHERE is_premium) as premium_users
    FROM users
    GROUP BY month
)
SELECT
    month,
    users,
    premium_users,
    ROUND(100.0 * premium_users / users, 2) as premium_pct
FROM monthly_stats
ORDER BY month DESC

%%calliope run-sql staging_db
UPDATE products
SET category = 'Electronics'
WHERE category = 'Tech';

SELECT * FROM products WHERE category = 'Electronics';

%%calliope run-sql mydb
SELECT * FROM transactions WHERE amount > 1000

# Use the results
import pandas as pd
df = pd.DataFrame(_calliope_last_data)

large_transactions = df[df['amount'] > 5000]
print(f"Found {len(large_transactions)} very large transactions")

# List available tables
%calliope get-datasource mydb

%%calliope run-sql mydb
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'your_table'