PROMPT_EXPLAIN

Explain a query

The prompt_explain table function allows MotherDuck AI to analyze and explain SQL queries in plain English. This feature helps you understand complex queries, verify that a query does what you intend, and learn SQL concepts through practical examples.

tip

This function is particularly useful for understanding queries written by others or for automatically documenting your own queries for future reference.

Syntax

CALL prompt_explain('<SQL query>', [include_tables=['<table_name>', '<table_name>']]);

Parameters

Parameter	Required	Description
query	Yes	The SQL query to explain
include_tables	No	Array of table names to consider for context (defaults to all tables in current database). Can also be a dictionary in the format {'table_name': ['column1', 'column2']} to specify which columns to include for each table.

Example usage

Here are several examples using MotherDuck's sample Hacker News dataset from MotherDuck's sample data database.

Explaining a complex query

CALL prompt_explain('
SELECT COUNT(*) as domain_count, 
SUBSTRING(SPLIT_PART(url, ''//'', 2), 1, POSITION(''/'' IN SPLIT_PART(url, ''//'', 2)) - 1) as domain 
FROM hn.hacker_news
WHERE url IS NOT NULL GROUP BY domain ORDER BY domain_count DESC LIMIT 10;
');

Output: when you run a prompt_explain query, you'll receive a single-column table with a detailed explanation:

explanation

The query retrieves the top 10 most frequent domains from the url field in the hn.hacker_news table. It counts the occurrences of each domain by extracting the domain part from the URL (after the '//' and before the next '/'), groups the results by domain, and orders them in descending order of their count. The result includes the count of occurrences (domain_count) and the domain name itself (domain).

Using dictionary format for include_tables

You can specify which columns to include for each table using the dictionary format:

CALL prompt_explain('
SELECT u.id, u.name, COUNT(s.id) AS story_count
FROM hn.users u
LEFT JOIN hn.stories s ON u.id = s.user_id
GROUP BY u.id, u.name
HAVING COUNT(s.id) > 5
ORDER BY story_count DESC
LIMIT 20;
', include_tables={'hn.users': ['id', 'name'], 'hn.stories': ['id', 'user_id']});

This approach allows you to focus the explanation on only the relevant columns, which can be helpful for tables with many columns.

How it works

The prompt_explain function processes your query in several steps:

1. Parsing: analyzes the SQL syntax to understand the query structure
2. Schema analysis: examines the referenced tables and columns to understand the data model
3. Operation analysis: identifies the operations being performed (filtering, joining, aggregating, etc.)
4. Translation: converts the technical SQL into a clear, human-readable explanation
5. Context addition: adds relevant context about the purpose and expected results of the query

Best practices

For the best results with prompt_explain:

1. Provide complete queries: include all parts of the query for the most accurate explanation
2. Use table aliases consistently: this helps the function understand table relationships
3. Specify relevant tables: use the include_tables parameter for large databases
4. Review explanations: verify that the explanation matches your understanding of the query
5. Use for documentation: save explanations as comments in your code for future reference

Notes

MotherDuck AI operates on your current database by evaluating the schemas and contents of the database. You can specify which tables and columns should be considered using the optional include_tables parameter. By default, all tables in the current database are considered.

To point MotherDuck AI at a specific database, execute the USE database command (learn more about switching databases).

These capabilities are provided by MotherDuck's integration with OpenAI. For availability and pricing, see MotherDuck's Pricing Model.

If you need higher usage limits or have specific requirements, please reach out to the Slack support channel or email support@motherduck.com.