Use Case Guides

Data Analysis

WhoDB is a powerful tool for data analysts, business intelligence professionals, and anyone who needs to extract insights from databases. Whether you're answering business questions, preparing data for reports, or investigating anomalies, WhoDB provides the tools to analyze data efficiently.

Tip

This guide covers filtering, querying, exporting, and common analysis patterns you'll use daily.

Data Analysis Workflow

Phase 1: Explore and Filter

Start by understanding what data you're working with:

Navigate to Your Table

Find and open the table containing the data you want to analyze.

Data View

The data grid shows the current page of rows in a familiar spreadsheet-like format. Use page size, pagination, and WHERE conditions to control how much data is loaded.

Apply Basic Filters

Use the filter interface to narrow down to relevant records.

Filter UI

Start with simple conditions:

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

The filter interface supports all comparison operators:

Equals (=)
Not Equals (!=)
Greater Than (>)
Less Than (<)
Greater Than or Equal (>=)
Less Than or Equal (<=)

Combine Conditions

Chain multiple filters together to create complex conditions:

Multiple Conditions

You can use AND logic to combine conditions:

status = 'active' AND created_at > '2024-01-01' AND amount > 100

This focuses your analysis on exactly the subset you need.

Search for Specific Values

Use the search feature to find specific text across all columns.

Quick searches are useful for:

Finding a specific user by name or email
Locating error messages or anomalous values
Checking if specific data exists in the table

Adjust Pagination

Control how many rows appear per page for better performance.

Page Size Dropdown

Small tables (< 100 rows): Use 50-100 rows per page for better overview
Large tables (> 1M rows): Use 10-25 rows per page to keep the interface responsive
Filtered data: May be able to use larger page sizes since you've already reduced the result set

Phase 2: Sort and Organize

Once you have the right subset, organize it for analysis:

Sort by Primary Column

Click a column header to sort data. Click again to reverse the sort order.

For analysis, sort by:

Date columns: Most recent first (DESC) or chronologically (ASC)
Amount columns: Largest first (DESC) to see extreme values
Category columns: Alphabetically to group similar items
Status columns: To group records by state

Multi-Column Sort

For complex analysis, sort by multiple columns:

Tip

Hold Shift while clicking column headers to add additional sort levels

Example: Sort by category then amount DESC to see:

All product categories grouped together
Within each category, highest amounts first

This reveals patterns like which categories have the most expensive items.

Scan for Patterns

With properly sorted and filtered data, look for:

Outliers: Extreme values at the top or bottom
Gaps: Missing values or unusual jumps
Clusters: Groups of similar values
Sequences: Logical progressions or patterns

Phase 3: Run Analysis Queries

For sophisticated analysis, switch to the Scratchpad to write custom SQL:

Open the Scratchpad

Navigate to the Scratchpad view or open the embedded scratchpad from your data view.

Scratchpad Code Editor

The Scratchpad is a full SQL development environment with syntax highlighting and execution controls.

Write Analysis Queries

Use SQL to answer specific questions about your data.

Example 1: Summary Statistics

sql

SELECT
  COUNT(*) as total_count,
  COUNT(DISTINCT user_id) as unique_users,
  AVG(amount) as average_amount,
  MIN(amount) as minimum_amount,
  MAX(amount) as maximum_amount,
  STDDEV(amount) as std_deviation
FROM orders
WHERE status = 'completed'
AND created_at > '2024-01-01';

This reveals the scale and distribution of your data.

Example 2: Time Series Analysis

sql

SELECT
  DATE_TRUNC('month', created_at) as month,
  COUNT(*) as order_count,
  SUM(amount) as total_revenue,
  AVG(amount) as avg_order_value
FROM orders
WHERE created_at > '2023-01-01'
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month DESC;

This shows trends over time, revealing seasonal patterns or growth/decline.

Example 3: Category Analysis

sql

SELECT
  category,
  COUNT(*) as product_count,
  SUM(sales) as total_sales,
  AVG(price) as avg_price,
  MAX(price) as max_price
FROM products
WHERE active = true
GROUP BY category
ORDER BY total_sales DESC;

This breaks down performance by category, identifying your best and worst performers.

Example 4: Cohort Analysis

sql

SELECT
  DATE_TRUNC('month', signup_date) as cohort_month,
  DATE_TRUNC('month', last_active) as activity_month,
  COUNT(*) as user_count
FROM users
WHERE signup_date IS NOT NULL
GROUP BY DATE_TRUNC('month', signup_date), DATE_TRUNC('month', last_active)
ORDER BY cohort_month DESC, activity_month DESC;

This tracks user retention by cohort—showing which signup cohorts remain active.

Example 5: Anomaly Detection

sql

WITH stats AS (
  SELECT
    AVG(amount) as avg_amount,
    STDDEV(amount) as std_dev
  FROM transactions
  WHERE created_at > NOW() - INTERVAL '30 days'
)
SELECT
  t.*
FROM transactions t, stats
WHERE ABS(t.amount - stats.avg_amount) > (3 * stats.std_dev)
ORDER BY t.created_at DESC;

This finds unusual values that deviate more than 3 standard deviations—likely anomalies or fraud.

View Results

Execute the query to see your analysis results.

Query Results

The results appear in a data grid below your query, using the same sorting and pagination features as the main data view.

Refine Your Query

Based on the results, refine your query:

Add or adjust filters
Include additional columns
Try different time periods
Adjust grouping or aggregation

Iteration is key to effective analysis.

Save for Later

Executed queries are available in the current cell's history.

Query History

For important analysis SQL, keep a copy in your team's normal documentation or repository. If you work in the CLI, use CLI bookmarks for named reusable queries.

Common Analysis Query Patterns

Aggregations & Summaries

Use GROUP BY to summarize data by category:

sql

-- Revenue by customer tier
SELECT
  customer_tier,
  COUNT(*) as customer_count,
  SUM(lifetime_value) as total_value,
  AVG(lifetime_value) as avg_customer_value
FROM customers
GROUP BY customer_tier
ORDER BY total_value DESC;

Tip

GROUP BY queries are the foundation of most business analytics—use them constantly to break down data into meaningful segments.

Comparisons & Benchmarking

Compare performance across segments:

sql

-- Compare this month vs last month
SELECT
  EXTRACT(MONTH FROM created_at) as month,
  EXTRACT(YEAR FROM created_at) as year,
  COUNT(*) as sales_count,
  SUM(amount) as revenue
FROM sales
WHERE created_at > NOW() - INTERVAL '2 months'
GROUP BY EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at)
ORDER BY year DESC, month DESC;

Ranking & Top-N Analysis

Find your best and worst performers:

sql

-- Top 10 products by revenue
SELECT
  product_name,
  SUM(quantity * price) as revenue,
  COUNT(*) as order_count,
  ROW_NUMBER() OVER (ORDER BY SUM(quantity * price) DESC) as rank
FROM order_items
GROUP BY product_name
ORDER BY rank
LIMIT 10;

Joins for Cross-Table Analysis

Combine data from multiple tables:

sql

-- Customers with their order summary
SELECT
  c.customer_id,
  c.customer_name,
  COUNT(o.order_id) as total_orders,
  SUM(o.order_total) as lifetime_value,
  MAX(o.created_at) as last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY lifetime_value DESC;

This pattern combines customer data with their order history in a single view.

Window Functions for Context

Calculate metrics while maintaining row detail:

sql

-- Orders with running total and percentile rank
SELECT
  order_id,
  amount,
  SUM(amount) OVER (ORDER BY created_at) as running_total,
  PERCENT_RANK() OVER (ORDER BY amount) * 100 as percentile
FROM orders
WHERE created_at > '2024-01-01'
ORDER BY created_at DESC;

Window functions let you see individual records alongside aggregate metrics—perfect for context.

Exporting Analysis Results

Once you have your analysis, export the data for sharing or further processing:

Choose The Export Entry Point

Choose the entry point that matches what you want to download:

Export Dialog

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

For analysis, apply the WHERE conditions first so the exported rows match the dataset you reviewed.

Choose Export Format

Select the format that best fits your downstream use:

Export Format

Configure Export Options

Customize the export to your needs:

Format: CSV or Excel (JSON Lines is offered for NoSQL-style sources)

Delimiter Options (for CSV):

Comma (standard, universal)
Semicolon (common in European systems)
Pipe | (good for data with commas)

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

Download Your Export

Click the Export button to download your file to your computer.

The file downloads immediately and is ready for use in your analysis tools, spreadsheets, or dashboards.

Analysis Best Practices

Troubleshooting Common Analysis Issues

Advanced Analysis Scenarios

Scenario 1: Churn Analysis

Identify which customers are at risk of leaving:

sql

-- Customers who haven't ordered recently
SELECT
  c.customer_id,
  c.customer_name,
  MAX(o.created_at) as last_order_date,
  NOW() - MAX(o.created_at) as days_since_order,
  COUNT(o.order_id) as lifetime_orders,
  SUM(o.amount) as lifetime_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING MAX(o.created_at) < NOW() - INTERVAL '90 days'
ORDER BY days_since_order DESC;

Export these results to a customer success team for re-engagement campaigns.

Scenario 2: Data Quality Audit

Find data anomalies and quality issues:

sql

-- Records with potential quality issues
SELECT
  *,
  CASE
    WHEN email NOT LIKE '%@%.%' THEN 'Invalid email'
    WHEN phone_number IS NULL THEN 'Missing phone'
    WHEN created_at > NOW() THEN 'Future date'
    WHEN length(first_name) > 50 THEN 'Suspiciously long name'
    ELSE 'OK'
  END as quality_issue
FROM users
WHERE
  email NOT LIKE '%@%.%'
  OR phone_number IS NULL
  OR created_at > NOW()
  OR length(first_name) > 50
ORDER BY created_at DESC;

Export and share with the data team to fix underlying data entry processes.

Scenario 3: Cohort Retention

Track how user retention changes by signup cohort:

sql

WITH cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('month', created_at)::date as signup_month
  FROM users
),
activity AS (
  SELECT
    user_id,
    DATE_TRUNC('month', activity_date)::date as activity_month
  FROM user_activity
)
SELECT
  c.signup_month,
  EXTRACT(MONTH FROM a.activity_month - c.signup_month) as months_since_signup,
  COUNT(DISTINCT c.user_id) as active_users
FROM cohorts c
LEFT JOIN 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 whether newer cohorts have better or worse retention than historical cohorts.

Next Steps

Now that you're confident with data analysis, explore:

Testing & Development

Use these same techniques with generated mock data

Database Exploration

Explore new datasets before analyzing them

Query Reference

Master advanced SQL techniques

Export Options

Learn export customization in depth

Check

You now have a complete toolkit for data analysis. From filtering and sorting through complex SQL queries to exporting results, you can answer virtually any question your data can reveal. The combination of interactive filtering, powerful SQL execution, and flexible export makes WhoDB an ideal analysis companion.

Database Exploration

Testing & Development

Data Analysis

Data Analysis Workflow

Phase 1: Explore and Filter

Phase 2: Sort and Organize

Phase 3: Run Analysis Queries

Common Analysis Query Patterns

Aggregations & Summaries

Comparisons & Benchmarking

Ranking & Top-N Analysis

Joins for Cross-Table Analysis

Window Functions for Context

Exporting Analysis Results

Analysis Best Practices

Start with Aggregates

Validate with Row Counts

Use Date Ranges

Create Intermediate Views

Cross-Check Results

Document Your Assumptions

Use Read-Only Connections When Possible

Troubleshooting Common Analysis Issues

Query Runs Too Slowly

Results Don't Match Expectations

Export File is Too Large

Data Looks Wrong After Export

Advanced Analysis Scenarios

Scenario 1: Churn Analysis

Scenario 2: Data Quality Audit

Scenario 3: Cohort Retention

Next Steps