Role-Based Guides

WhoDB for Data Analysts

As a data analyst, you need a powerful tool that bridges the gap between raw databases and actionable insights. WhoDB provides everything you need: interactive filtering, advanced SQL capabilities, complex query execution, and flexible data export—all without leaving your browser.

Tip

This guide covers analytics workflows, complex query patterns, performance optimization, and best practices for turning database queries into business insights.

Analytics Workflow Overview

The typical analytics workflow in WhoDB follows four phases: explore, analyze, validate, and export.

Explore Data

Understand table structure, identify relevant data, apply initial filters

Analyze & Query

Write complex SQL, aggregate data, find patterns and trends

Validate Results

Verify queries, cross-check results, ensure data accuracy

Export & Share

Export in multiple formats, prepare for stakeholders and dashboards

Phase 1: Data Exploration

Before writing complex queries, understand what data you're working with.

Interactive Table Exploration

Navigate to Your Table

Select the table containing your analysis data.

Data View

The data grid displays your records in a familiar spreadsheet format, making it easy to scan and understand the data structure.

Examine Column Types and Values

Review each column:

Data types: Are dates formatted correctly? Are numbers in the expected range?
Value distributions: What values appear most frequently? Are there nulls?
Data quality: Do you see obvious errors or anomalies?

This preliminary scan informs your analysis approach.

Apply Initial Filters

Start with simple filtering to narrow your focus:

Filter UI

Example filters:

status = 'active' to focus on current records
created_at >= '2024-01-01' to focus on recent periods
amount > 0 to exclude zero-value records
is_deleted = false to remove archived data

Basic filters help you understand your dataset's composition.

Search for Specific Patterns

Use search to locate specific values across columns:

Search is useful for:

Finding specific user accounts or transactions
Locating error messages or anomalous data
Verifying whether specific data exists
Quick spot-checks during analysis

Understanding Relationships

Navigate to related tables to understand your data model:

Explore Foreign Keys

Identify which columns link to other tables. This tells you:

What related data is available
How to write effective joins
What aggregations make sense

Example: An orders table with a user_id foreign key tells you that order data can be linked to user data.

Check Table Dependencies

Understanding relationships helps you:

Write joins efficiently
Validate aggregation logic
Identify data consistency issues

Example: If a customer table has 5,000 records but orders table shows 6,000 unique customer IDs, you have orphaned orders.

Phase 2: Complex Analysis Queries

Once you understand your data, write sophisticated queries to extract insights.

Opening the Query Interface

Access the Scratchpad

Open the Scratchpad view for full SQL development capabilities:

Scratchpad Code Editor

The Scratchpad provides:

Full SQL syntax highlighting
Per-cell query history
Result pagination and sorting
Multiple query cells for iterative analysis

Write Your First Query

Start simple and build complexity:

sql

SELECT
  COUNT(*) as total_records,
  COUNT(DISTINCT user_id) as unique_users,
  AVG(amount) as average_amount
FROM orders
WHERE created_at >= '2024-01-01';

This baseline query tells you the scale and scope of your dataset.

Essential Analytics Queries

Aggregation & Summarization

Group data to find patterns and trends:

sql

-- Sales by product category (most common analytics pattern)
SELECT
  category,
  COUNT(*) as transaction_count,
  SUM(amount) as total_revenue,
  AVG(amount) as avg_transaction_size,
  MAX(amount) as max_transaction,
  MIN(amount) as min_transaction,
  STDDEV(amount) as revenue_volatility
FROM orders
WHERE created_at >= '2024-01-01'
  AND status = 'completed'
GROUP BY category
ORDER BY total_revenue DESC;

Tip

GROUP BY queries are the foundation of analytics. Use them constantly to break data into meaningful segments.

Time Series Analysis

Track metrics over time to identify trends:

sql

-- Monthly revenue trend with growth rate
WITH monthly_sales AS (
  SELECT
    DATE_TRUNC('month', created_at)::date as month,
    SUM(amount) as revenue,
    COUNT(*) as transaction_count
  FROM orders
  WHERE status = 'completed'
    AND created_at >= '2023-01-01'
  GROUP BY DATE_TRUNC('month', created_at)
)
SELECT
  month,
  revenue,
  transaction_count,
  LAG(revenue) OVER (ORDER BY month) as previous_month_revenue,
  ROUND(((revenue - LAG(revenue) OVER (ORDER BY month)) /
         LAG(revenue) OVER (ORDER BY month) * 100)::numeric, 2) as growth_percent
FROM monthly_sales
ORDER BY month DESC;

This reveals whether your metrics are growing, stable, or declining.

Cohort Analysis

Track how different user groups behave over time:

sql

-- User retention by signup cohort
WITH user_cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('month', created_at)::date as signup_month
  FROM users
),
user_activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', activity_date)::date as activity_month
  FROM user_events
  WHERE event_type = 'purchase'
)
SELECT
  c.signup_month,
  EXTRACT(YEAR FROM AGE(a.activity_month, c.signup_month)) * 12 +
  EXTRACT(MONTH FROM AGE(a.activity_month, c.signup_month)) as months_since_signup,
  COUNT(DISTINCT c.user_id) as active_users
FROM user_cohorts c
LEFT JOIN user_activity a ON c.user_id = a.user_id
GROUP BY c.signup_month, months_since_signup
ORDER BY c.signup_month DESC, months_since_signup;

This shows which signup cohorts have the best retention and engagement.

Join-Based Analysis

Combine data from multiple tables:

sql

-- Customer lifetime value analysis
SELECT
  c.customer_id,
  c.customer_name,
  c.segment,
  COUNT(o.order_id) as lifetime_orders,
  SUM(o.amount) as lifetime_value,
  AVG(o.amount) as avg_order_value,
  MAX(o.created_at) as last_purchase_date,
  MIN(o.created_at) as first_purchase_date,
  DATEDIFF(MAX(o.created_at), MIN(o.created_at)) as customer_tenure_days
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.segment
ORDER BY lifetime_value DESC;

This powerful pattern combines customer attributes with their transaction history.

Window Functions for Context

Calculate metrics while preserving row-level detail:

sql

-- Orders ranked by amount, with percentile context
SELECT
  order_id,
  customer_id,
  amount,
  ROW_NUMBER() OVER (ORDER BY amount DESC) as rank,
  PERCENT_RANK() OVER (ORDER BY amount) * 100 as percentile,
  SUM(amount) OVER (ORDER BY amount DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total
FROM orders
WHERE created_at >= '2024-01-01'
ORDER BY amount DESC
LIMIT 100;

Window functions let you see individual records alongside aggregate context.

Anomaly Detection

Find unusual values that deviate from normal patterns:

sql

-- Detect transactions that are statistical outliers
WITH transaction_stats AS (
  SELECT
    AVG(amount) as avg_amount,
    STDDEV(amount) as std_dev
  FROM orders
  WHERE created_at >= NOW() - INTERVAL '90 days'
)
SELECT
  o.order_id,
  o.customer_id,
  o.amount,
  ts.avg_amount,
  ROUND((o.amount - ts.avg_amount) / ts.std_dev, 2) as std_deviations_from_mean,
  CASE
    WHEN ABS(o.amount - ts.avg_amount) > (3 * ts.std_dev) THEN 'ANOMALY'
    WHEN ABS(o.amount - ts.avg_amount) > (2 * ts.std_dev) THEN 'UNUSUAL'
    ELSE 'NORMAL'
  END as classification
FROM orders o, transaction_stats ts
WHERE ABS(o.amount - ts.avg_amount) > (2 * ts.std_dev)
ORDER BY ABS(o.amount - ts.avg_amount) DESC;

This finds outliers that might indicate fraud, data errors, or significant business events.

Query Execution & Results

Execute Your Query

Click the Execute button or press Ctrl+Enter:

Query Results

The query runs and results appear below your query text.

Review Results

Examine the result set:

Row count: Does the number of rows make sense?
Value ranges: Are values within expected ranges?
Null values: Are nulls appearing where expected?
Data types: Do columns display correct types?

Sort Results

Click column headers to sort:

Sales by Category (sorted descending by revenue):
Electronics | 5,400 | $850,000
Furniture   | 3,200 | $620,000
Clothing    | 8,100 | $580,000

Sorting helps identify top performers and outliers.

Multi-Cell Queries

For complex analysis, break your work into multiple cells:

Create Intermediate Queries

Build analysis step-by-step. Each cell runs as an independent, self-contained statement, so include everything a cell needs — including its CTEs — within that cell:

Multiple Cells

Cell 1: Inspect the cleaned, filtered data

sql

-- Get valid orders from the past year
SELECT *
FROM orders
WHERE status = 'completed'
  AND created_at >= '2023-01-01'
  AND amount > 0;

Cell 2: Aggregate into meaningful metrics (repeat the filter in a CTE)

sql

-- Calculate metrics by category
WITH valid_orders AS (
  SELECT *
  FROM orders
  WHERE status = 'completed'
    AND created_at >= '2023-01-01'
    AND amount > 0
)
SELECT category, COUNT(*) as count, SUM(amount) as revenue
FROM valid_orders
GROUP BY category;

Cell 3: Format for presentation (chain the CTEs in one statement)

sql

-- Add rankings and percentages
WITH valid_orders AS (
  SELECT *
  FROM orders
  WHERE status = 'completed'
    AND created_at >= '2023-01-01'
    AND amount > 0
),
category_metrics AS (
  SELECT category, COUNT(*) as count, SUM(amount) as revenue
  FROM valid_orders
  GROUP BY category
)
SELECT *,
  ROW_NUMBER() OVER (ORDER BY revenue DESC) as rank,
  ROUND(revenue / SUM(revenue) OVER () * 100, 1) as revenue_percent
FROM category_metrics;

This step-by-step approach is easier to debug and modify: earlier cells validate each stage, and the final cell contains the complete, self-contained query.

Use Query History

Access previous queries instantly:

Query History

Your query history lets you:

Find previously successful queries
Load and modify previous SQL in the current editor
Track your analysis progression
Recreate analysis later

Phase 3: Data Validation

Before sharing analysis results, validate your queries.

Validation Checklist

Once validated, export your analysis results.

Export Workflow

Select Your Data

Choose what to export:

Export Dialog

Options:

All: Export from the table or header context menu
Selected: Select loaded rows first, then export selected rows
Filtered: Apply WHERE conditions before exporting

Choose Your Format

Select the format that matches your audience and downstream use:

Export Format

Configure Export Options

Customize the export:

Format: CSV or Excel (JSON Lines is offered for NoSQL-style sources)
CSV delimiter: Comma (standard), semicolon (European), or pipe (data with commas)

To export a subset of columns, select only those columns in your query before exporting.

Download and Verify

Download your file and do a quick sanity check:

Row count matches query results
Column order is correct
Data formatting is readable
No encoding issues with special characters

Real-World Analytics Scenarios

Scenario 1: Customer Segmentation

Identify different customer groups for targeted marketing:

sql

-- Segment customers by behavior
SELECT
  c.customer_id,
  c.customer_name,
  c.segment_category,
  COUNT(o.order_id) as lifetime_orders,
  SUM(o.amount) as lifetime_value,
  MAX(o.created_at) as last_purchase,
  DATEDIFF(NOW(), MAX(o.created_at)) as days_since_purchase,
  CASE
    WHEN SUM(o.amount) > 5000 AND DATEDIFF(NOW(), MAX(o.created_at)) < 30 THEN 'High Value Active'
    WHEN SUM(o.amount) > 5000 AND DATEDIFF(NOW(), MAX(o.created_at)) < 90 THEN 'High Value At Risk'
    WHEN SUM(o.amount) > 1000 AND DATEDIFF(NOW(), MAX(o.created_at)) < 30 THEN 'Medium Value Active'
    WHEN SUM(o.amount) <= 1000 AND COUNT(o.order_id) >= 5 THEN 'Small Value Loyal'
    WHEN COUNT(o.order_id) = 1 THEN 'One-Time Buyer'
    ELSE 'Dormant'
  END as customer_segment
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.segment_category
ORDER BY lifetime_value DESC;

Export this for marketing teams to enable targeted campaigns.

Scenario 2: Churn Prediction

Identify customers at risk of leaving:

sql

-- Find customers showing churn signals
WITH customer_activity AS (
  SELECT
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) as total_orders,
    MAX(o.created_at) as last_order_date,
    DATEDIFF(NOW(), MAX(o.created_at)) as days_inactive,
    AVG(o.amount) as avg_order_value,
    STDDEV(o.amount) as order_volatility
  FROM customers c
  LEFT JOIN orders o ON c.customer_id = o.customer_id
  GROUP BY c.customer_id, c.customer_name
)
SELECT
  *,
  CASE
    WHEN days_inactive > 180 AND total_orders < 5 THEN 'HIGH RISK'
    WHEN days_inactive > 90 AND total_orders < 10 THEN 'MEDIUM RISK'
    WHEN days_inactive > 60 THEN 'LOW RISK'
    ELSE 'STABLE'
  END as churn_risk
FROM customer_activity
WHERE days_inactive > 60
ORDER BY days_inactive DESC;

Scenario 3: Product Performance Analysis

Identify your best and worst performing products:

sql

-- Detailed product performance metrics
SELECT
  p.product_id,
  p.product_name,
  p.category,
  COUNT(DISTINCT oi.order_id) as times_sold,
  SUM(oi.quantity) as total_units_sold,
  SUM(oi.quantity * oi.price) as total_revenue,
  AVG(oi.price) as avg_price,
  MIN(oi.price) as min_price,
  MAX(oi.price) as max_price,
  ROUND(SUM(oi.quantity * oi.price) / NULLIF(COUNT(DISTINCT oi.order_id), 0), 2) as revenue_per_sale,
  ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity * oi.price) DESC) as rank_in_category
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name, p.category
ORDER BY total_revenue DESC;

Performance Optimization for Large Datasets

Query Optimization

Troubleshooting Query Issues

Best Practices for Analytics

Start with Questions

Begin with a specific business question, not just exploration. This focuses your analysis.

Validate Your Data

Cross-check results different ways. Verify row counts and value ranges.

Document Assumptions

When exporting, document date ranges, filters applied, and what NULLs mean.

Use Read-Only Access

When possible, connect to read-only replicas to avoid accidental data modification.

Use Query History

Reuse queries from Scratchpad history, and store important query files in your normal documentation or repository.

Iterate Incrementally

Build complex queries step-by-step. Test each piece before adding more complexity.

Next Steps

Continue mastering WhoDB analytics:

Database Exploration

Learn how to explore and understand new datasets

Advanced Queries

Master complex SQL patterns and optimization

Data Export

Learn export customization for different audiences

Where Conditions

Master filtering techniques for focused analysis

Check

You now have a complete toolkit for data analysis in WhoDB. From exploring raw data through complex SQL queries to exporting polished results, you can answer virtually any question your database holds. The combination of interactive exploration, powerful query execution, and flexible export makes WhoDB an essential analytics companion.

Developers

WhoDB for Database Administrators

WhoDB for Data Analysts

Analytics Workflow Overview

Phase 1: Data Exploration

Interactive Table Exploration

Understanding Relationships

Phase 2: Complex Analysis Queries

Opening the Query Interface

Essential Analytics Queries

Aggregation & Summarization

Time Series Analysis

Cohort Analysis

Join-Based Analysis

Window Functions for Context

Anomaly Detection

Query Execution & Results

Multi-Cell Queries

Phase 3: Data Validation

Validation Checklist

Row Count Validation

Value Range Validation

NULL Handling Validation

Join Validation

Aggregate Validation

Temporal Validation

Phase 4: Exporting & Sharing

Export Workflow

Real-World Analytics Scenarios

Scenario 1: Customer Segmentation

Scenario 2: Churn Prediction

Scenario 3: Product Performance Analysis

Performance Optimization for Large Datasets

Query Optimization

Index Usage

Reduce Scope

Simplify Joins

Use CTEs for Clarity

Troubleshooting Query Issues

Query Runs Too Slowly

Results Don't Match Expectations

Rows Multiplied by Join

Best Practices for Analytics

Next Steps