SQL Playground

The SQL Playground is GoPie's powerful SQL editor designed for data professionals who want direct control over their queries. With features like intelligent auto-completion, real-time validation, and performance insights, it's the perfect environment for complex data analysis.

Editor Features

Intelligent Auto-Completion

The editor provides context-aware suggestions as you type:

• Table Names - All available tables in your datasets
• Column Names - Columns with data types and descriptions
• SQL Keywords - Syntax highlighting and completion
• Functions - Database-specific and standard SQL functions
• Snippets - Common query patterns and templates

Syntax Highlighting

The editor uses semantic highlighting to make your code readable:

-- Keywords in blue
SELECT 
  -- Functions in purple
  COUNT(DISTINCT customer_id) as unique_customers,
  -- Strings in green
  CASE 
    WHEN total_spent > 1000 THEN 'Premium'
    WHEN total_spent > 500 THEN 'Standard'
    ELSE 'Basic'
  END as customer_tier,
  -- Numbers in orange
  ROUND(AVG(order_value), 2) as avg_order_value
FROM orders
-- Comments in gray
WHERE order_date >= '2024-01-01'
GROUP BY customer_tier
HAVING COUNT(*) > 10
ORDER BY unique_customers DESC;

Multi-Cursor Editing

Boost productivity with multi-cursor support:

• Alt+Click - Add cursor at clicked position
• Ctrl+Alt+Up/Down - Add cursor above/below
• Ctrl+D - Select next occurrence
• Ctrl+Shift+L - Select all occurrences

Code Folding

Manage complex queries by folding sections:

• Click the fold indicators in the gutter
• Ctrl+Shift+[ - Fold current block
• Ctrl+Shift+] - Unfold current block
• Ctrl+K Ctrl+0 - Fold all
• Ctrl+K Ctrl+J - Unfold all

Query Execution

Running Queries

Multiple ways to execute your SQL:

-- All three queries will run
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM products;

SELECT customer_id, SUM(amount) as total
FROM orders
-- Select and run just this WHERE clause for testing
WHERE order_date >= '2024-01-01'
GROUP BY customer_id;

-- Cursor here runs first query
SELECT * FROM users LIMIT 10;

-- Cursor here runs second query  
SELECT * FROM orders LIMIT 10;

Query Parameters

Use parameters for dynamic queries:

-- Define parameters with @
SELECT * FROM orders 
WHERE order_date >= @start_date
  AND order_date <= @end_date
  AND status = @order_status;

When you run this query, GoPie will prompt for parameter values.

Query Results

Results appear below the editor with:

• Data Grid - Sortable, filterable result table
• Row Count - Total rows returned
• Execution Time - Query performance metrics
• Export Options - Download results in various formats

Schema Browser

The left sidebar provides easy schema navigation:

Database Explorer

• Tables - All tables with row counts
• Views - Available database views
• Columns - Data types and nullable info
• Indexes - Performance optimization hints
• Relationships - Foreign key connections

Quick Actions

Right-click any table for quick actions:

• Preview Data - SELECT * LIMIT 100
• Count Rows - SELECT COUNT(*)
• Generate INSERT - Template for new data
• Generate SELECT - All columns query
• View Definition - Table create statement

Search Schema

Use the search box to quickly find:

• Tables by name
• Columns across all tables
• Specific data types
• Column descriptions

Query Management

Saving Queries

Save frequently used queries:

1. Write your query
2. Click "Save Query" or press Ctrl+S
3. Provide:
   • Name - Descriptive title
   • Description - What it does
   • Tags - For organization
   • Folder - Categorize queries

Query Library

Access saved queries from the library:

• Personal Queries - Your saved queries
• Team Queries - Shared by teammates
• Public Templates - Common patterns
• Recent Queries - Auto-saved history

Version Control

Track query changes:

• Automatic version history
• Compare versions side-by-side
• Restore previous versions
• See who made changes

Performance Insights

Query Explain

Understand query performance:

EXPLAIN ANALYZE
SELECT c.name, COUNT(o.id) as order_count
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.created_at >= '2024-01-01'
GROUP BY c.name
ORDER BY order_count DESC;

The explain plan shows:

• Execution steps
• Row estimates
• Index usage
• Join methods
• Sort operations

Performance Tips

Query Optimization

The editor provides optimization suggestions:

• Missing index warnings
• Inefficient join patterns
• Cartesian product alerts
• Subquery optimization hints

Advanced Features

Keyboard Shortcuts

SQL Formatting

Auto-format your queries:

Before:

select c.name,count(*) as cnt,sum(o.amount) from customers c join orders o on c.id=o.customer_id where o.status='completed' group by c.name having count(*)>5 order by cnt desc

After formatting (Shift+Alt+F):

SELECT 
  c.name,
  COUNT(*) as cnt,
  SUM(o.amount)
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.name
HAVING COUNT(*) > 5
ORDER BY cnt DESC

Code Snippets

Use built-in snippets for common patterns:

Type sel and press Tab:

SELECT 
  $1
FROM $2
WHERE $3

Common snippets:

• sel - SELECT statement
• joi - JOIN clause
• cte - Common Table Expression
• ins - INSERT statement
• upd - UPDATE statement
• grp - GROUP BY with aggregates

Query Variables

Define reusable variables:

-- Define variables at the top
SET @start_date = '2024-01-01';
SET @end_date = '2024-12-31';
SET @min_amount = 100;

-- Use throughout your query
SELECT 
  DATE_TRUNC('month', order_date) as month,
  SUM(amount) as total
FROM orders
WHERE order_date BETWEEN @start_date AND @end_date
  AND amount >= @min_amount
GROUP BY month;

Integration Features

Export Results

Export query results in multiple formats:

• CSV - For Excel and data tools
• JSON - For applications
• Parquet - For big data platforms
• Excel - Formatted spreadsheets
• Clipboard - Quick copy/paste

Create API Endpoint

Turn any query into an API:

1. Write and test your query
2. Click "Create API"
3. Configure:
   • Endpoint name
   • Parameters
   • Authentication
   • Rate limits

Schedule Queries

Set up recurring query execution:

• Daily/weekly/monthly schedules
• Email results to stakeholders
• Trigger webhooks on completion
• Update materialized views

Tips and Tricks

1. Use CTEs for Readability

WITH monthly_sales AS (
  SELECT 
    DATE_TRUNC('month', order_date) as month,
    SUM(amount) as total
  FROM orders
  GROUP BY month
),
growth_rates AS (
  SELECT 
    month,
    total,
    LAG(total) OVER (ORDER BY month) as prev_month,
    (total - LAG(total) OVER (ORDER BY month)) / LAG(total) OVER (ORDER BY month) * 100 as growth_rate
  FROM monthly_sales
)
SELECT * FROM growth_rates
WHERE growth_rate > 10;

2. Window Functions for Analytics

SELECT 
  customer_id,
  order_date,
  amount,
  -- Running total
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) as running_total,
  -- Rank within customer
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) as order_rank,
  -- Percentage of customer's total
  amount / SUM(amount) OVER (PARTITION BY customer_id) * 100 as pct_of_total
FROM orders;

3. Query Comments for Documentation

-- Purpose: Monthly cohort retention analysis
-- Author: Data Team
-- Last Updated: 2024-01-15
-- Parameters: @cohort_start_date, @analysis_months

WITH cohorts AS (
  -- Define cohorts based on first purchase date
  SELECT 
    customer_id,
    DATE_TRUNC('month', MIN(order_date)) as cohort_month
  FROM orders
  GROUP BY customer_id
)
-- ... rest of query

Troubleshooting

Common Issues

Syntax Errors

• Check for missing commas in SELECT
• Ensure all parentheses are matched
• Verify column names exist

Performance Problems

• Add appropriate WHERE clauses
• Use LIMIT during development
• Check for missing JOIN conditions

Unexpected Results

• Verify JOIN conditions
• Check for NULL handling
• Ensure correct date ranges

Getting Help

• Hover for hints - Hover over errors for fixes
• Check docs - Press F1 for SQL reference
• Ask AI - Use chat for query help
• Team chat - Collaborate on complex queries

What's Next?

• Query History - Track and manage your queries
• Multi-Dataset Queries - Join across datasets
• Creating Charts - Visualize your results