PROMPT_QUERY

Answer questions about your data

The prompt_query pragma allows you to ask questions about your data in natural language. This feature translates your plain English questions into SQL, executes the query, and returns the results.

Under the hood, MotherDuck analyzes your database schema, generates appropriate SQL and executes the query on your behalf. This makes data exploration and analysis accessible to users of all technical levels.

For comprehensive guidance on building analytics agents, including best practices and implementation patterns, see Building Analytics Agents with MotherDuck.

info

The prompt_query pragma is a read-only operation and does not allow queries that modify the database.

Syntax

PRAGMA prompt_query('<natural language question>')

Parameters

Parameter	Required	Description
question	Yes	The natural language question about your data

Example usage

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

prompt_query can be used to answer both simple and complex questions.

Basic questions

-- Find the most shared domains
PRAGMA prompt_query('what are the top domains being shared on hacker_news?')

-- Analyze posting patterns
PRAGMA prompt_query('what day of the week has the most posts?')

-- Identify trends
PRAGMA prompt_query('how has the number of posts changed over time?')

Complex questions

-- Multi-part analysis
PRAGMA prompt_query('what are the top 5 domains with the highest average score, and how many stories were posted from each?')

-- Time-based analysis
PRAGMA prompt_query('compare the average score of posts made during weekdays versus weekends')

-- Conditional filtering
PRAGMA prompt_query('which users have posted the most stories about artificial intelligence or machine learning?')

Best practices

For the best results with prompt_query:

1. Be specific: clearly state what information you're looking for
2. Provide context: include relevant details about the data you want to analyze
3. Use natural language: phrase your questions as you would ask a data analyst
4. Start simple: begin with straightforward questions and build to more complex ones
5. Refine iteratively: if results aren't what you expected, try rephrasing your question

Limitations

While prompt_query is powerful, be aware of these limitations:

• Only performs read operations (SELECT queries)
• Works best with well-structured data with clear column names
• Complex statistical analyses will likely require you (or an LLM) to write SQL
• Performance depends on the complexity of your question and database size
• May not understand highly domain-specific terminology without you giving more context

Troubleshooting

If you're not getting the expected results:

• Check that you're connected to the correct database
• Ensure your question is clear and specific
• Try rephrasing your question using different terms
• For complex analyses, break down into multiple simpler questions