#sql Prompts

Analyze and optimize PostgreSQL query performance. Steps: 1. Identify slow queries with pg_stat_statements. 2. Analyze Execution plans (EXPLAIN ANALYZE). 3. Create covering indexes for frequent queries. 4. Optimize JOIN operations. 5. Vacuum and analyze strategy. 6. Partition large tables. 7. Tune c...

Design a dbt (data build tool) project for analytics engineering. Structure: 1. Staging models (raw data cleaning). 2. Intermediate models (business logic transformations). 3. Mart models (final aggregated tables). 4. Tests for data quality (unique, not_null, relationships). 5. Documentation with sc...

Design efficient database schemas. Normalization forms: 1. 1NF (atomic values, no repeating groups). 2. 2NF (no partial dependencies). 3. 3NF (no transitive dependencies). Balance normalization with performance (denormalize for read-heavy). Design principles: Use surrogate keys (auto-increment IDs)....

Generate a SQL schema for a simple e-commerce website. It should include tables for Products, Customers, Orders, and Order_Items. Define the columns for each table, specify data types, and set up primary and foreign key relationships.

I have a slow-running SQL query. Here is the query: [paste query here]. And here is the table schema: [paste schema here]. Analyze the query and suggest ways to optimize it. Consider adding indexes, rewriting the query, or denormalizing the data.

Debate the pros and cons of using an Object-Relational Mapper (ORM) like SQLAlchemy or TypeORM versus writing raw SQL queries. Discuss aspects like developer productivity, performance, security, and database abstraction. In what scenarios would you strongly prefer one over the other?

Optimize a slow-running SQL query on a 50M+ row table. Techniques to apply: 1. Add appropriate indexes on WHERE and JOIN columns. 2. Replace subqueries with CTEs (Common Table Expressions). 3. Use EXPLAIN ANALYZE to identify bottlenecks. 4. Partition large tables by date for faster scans. 5. Rewrite...