Top50 Database Queries

Once you've hooked up all your tables together, it's time to write a couple of queries. Well, a couple is for newbies. You're a pro now, so you'll need to write 50(!) queries for your database. And that's just the essentials.

Database Queries

1. Getting the product list for the storefront

This query returns all active products with their main price and image for display on the homepage and in the catalog. It lets you quickly build the storefront and keep product info up to date.

2. Product search by keyword

Lets users find products they're interested in by matching the name or description. This is a key part of user functionality for fast catalog search.

3. Product card by ID

Returns extended info for a specific product, including brand and category. Needed to show the product details page.

4. List of product variants

Shows all available product variants (SKU): sizes, colors, stock, prices. Used to pick the right option on the product page.

5. Product image gallery

To fully show the product card, you need all its photos. The query returns them with the main image marked.

6. Average rating and number of reviews for a product

Used to display the product's rating and review count, which is important for reputation and buyer trust.

7. Detailed list of product reviews

For the reviews section in the product card: rating, text, author, and review date. Helps new buyers decide whether to buy.

8. Product Q&A

Query to get questions and answers for each product, which is important for the Frequently Asked Questions block on the product page.

9. Product categories with hierarchy

Lets you visualize the catalog structure, build a navigation tree for filters and menus.

10. Products by category and subcategories

Helps display all products from a selected category or its "child" categories (nested levels).

11. List of brands

For filtering by brands, creating brand listings and landing pages.

12. Popular tags and their product counts

Analyzes the most used tags to show trending products and build a tag cloud.

13. Product price change history

For analytics and showing price dynamics (old/new price, promos).

14. Product status change history

Lets you track the product lifecycle, reason for disappearing from the storefront or being returned.

15. Search by certificates and licenses

Critical for pro buyers and B2B segment (product quality and legality).

16. Product supplier data

Important for admin, quality control, and supplier contact.

17. Product stock by warehouse

Control and track current stock by warehouse. Needed for logistics and to prevent "out of stock".

18. Products with stock below threshold

Automates restocking, prevents lost sales due to missing products.

19. Product movement in warehouse (audit)

Tracks all product movements for a selected period: receipts, write-offs, corrections—important for inventory and loss prevention.

20. Logistics of transfers between warehouses

Lets you see the history and status of internal product transfers between logistics centers.

21. Delivery: methods and rates

For calculating delivery cost and informing the user at checkout.

22. User order history

The most important part of the user account—all completed orders, their status, and amount.

23. Order details with items

Lets you get the full order structure—contents, prices, quantities—for display on the frontend or for support.

24. Orders report by period and status

Sales analytics and reporting, returns orders by period and needed status (like "completed").

25. "Abandoned" carts

Analytics for marketers: carts where the user didn't place an order—potential for retargeting.

26. Top sales

Analytics for the "Best Sellers" block and marketing picks: which products are bought most often.

27. Sales by day (for charts)

Daily revenue report—the basis for business dynamics analysis and chart building.

28. List of returns

Shows returns for all orders with reason and status, helps analyze return reasons.

29. List of order cancellations

Control of losses and cancellation reasons: shows cancellations with reason, who canceled, and when.

30. Orders waiting to be shipped

For warehouse and delivery service—orders that need to be packed and shipped, with delivery details.

31. Average order value

The "Average Order Value" metric—a key metric for evaluating marketing and assortment effectiveness.

32. Orders with promo codes applied

Promo effectiveness analytics: which promo codes were used and how often.

33. Discount usage by category and brand

Lets you see which promos work and monitor discount popularity by product category and brand.

34. Applied promo codes and their users

Control over promo code usage, detect anomalies and abuse.

35. Payment history for an order

For support and accounting: shows all payment transactions, their statuses, and payment methods used.

36. Orders with refunds

For analyzing returns, generating accounting reports, and preventing fraud.

37. User wallet balance and transaction history

Control and display of user bonus or cashback funds, history of their movement.

38. User support tickets

Lets the user see their support requests and their statuses.

39. SLA analytics for support tickets

Analyzes average response and resolution time by priority, important for SLA control.

40. Messages for a support ticket

Lets you see the whole conversation for a selected ticket, important for user and support.

41. Active FAQs by category

Shows frequently asked questions for the client knowledge base, helps reduce support load.

42. Active marketing campaigns and banners

For displaying current promo offers on the site.

43. Featured products on the homepage

For the "Featured" block: products to highlight on the homepage.

44. A/B test history

Analysis of experiments run to optimize UX and marketing.

45. Product view history for a specific user

Shows "You viewed" or is used for personalized recommendations.

46. Popular user search queries

User demand analysis, helps optimize search and suggestions.

47. Traffic source analytics

Lets you see which ad channels bring traffic and conversions.

48. User retention by cohorts

Key metric for loyalty and repeat purchase analysis.

49. News/articles for the homepage

For showing news and blog articles, boosting user engagement.

50. Active site pages and related content blocks

For checking site content integrity, CMS operation, and displaying data on pages.

Adding Indexes

Queries are cool, but only if they run fast. So you'll need to add a few indexes to your database. You should add 40 indexes to your project's main tables to boost query performance and make things easier to maintain.

1. Index on product.product(status)

Almost all product queries filter by status (like active products for the storefront, search, etc.). The index speeds up fetching products with a certain status, minimizing full table scans.

2. Index on product.variant(product_id, is_active)

Queries for product variants (SKU) and for the storefront use filtering by product link and variant activity. This composite index lets you optimally select all active variants for a specific product.

3. Index on product.image(product_id, is_main DESC)

To get the main product image (or the whole list), you filter by product and sort by "main" flag. The index speeds up these selections and ensures fast data for galleries.

4. Index on product.product(name text_pattern_ops)

For fast product search by keyword in the name using ILIKE '%...%'. A specialized index on name text_pattern_ops improves substring search, especially on large volumes.

5. Index on product.product(description gin_trgm_ops)

Same as #4—for searching product descriptions (ILIKE or full-text search). GIN index with trigrams speeds up filtering on text fields.

6. Index on product.product(category_id)

Often you select by category or by direct/child categories (see catalog category filter queries). The index lets you quickly find all products in a given category.

7. Index on product.category(parent_id)

For building category hierarchies and navigation trees, you often select by parent_id. The index speeds up these recursive hierarchical queries.

8. Index on product.review(product_id)

All review queries for a product filter by product_id (for average rating and review list). Indexing this field makes review aggregation and selection way faster.

9. Index on product.review(product_id, created_at DESC)

For quickly getting the latest product reviews (ORDER BY createdat DESC), especially with productid filtering, a composite index helps.

10. Index on product.question(product_id, created_at DESC)

Popular query for answers to a specific product, sorted by creation time. The index covers both conditions and speeds up Q&A section output in the product card.

11. Index on product.answer(question_id, created_at)

To find answers to product questions, you need fast access by question_id, often sorted by date. This index minimizes lag when generating Q&A.

12. Index on product.price_history(variant_id, changed_at DESC)

Price change history is quickly fetched by product variant and recent changes. This index speeds up analytics on price dynamics and "old/new price".

13. Index on product.status_history(product_id, changed_at DESC)

Fetching product status change history sorted by time is needed for audit and lifecycle control. Composite index speeds up these queries a lot.

14. Index on product.certificate(product_id)

Searching for product certificates by its id—a typical operation for B2B and certified storefronts. The index speeds up these checks.

15. Index on product.license(product_id)

For searching licenses by products, especially in queries filtering by license type.

16. Index on product.product_tag(tag_id)

Common query—get all products by a certain tag (and vice versa). The index lets you quickly cross products and tags for tag clouds or filters.

17. Index on product.product_tag(product_id)

Lets you quickly see which tags are linked to a specific product, speeding up tag-based selections.

18. Index on logistics.inventory(product_id, warehouse_id)

For instant access to product stock in a warehouse (or for all warehouses)—critical for logistics, stock level checks, and real-time storefronts.

19. Index on logistics.inventory(variant_id)

For tracking stock by specific product variant (color/size) and for cross-cutting reports.

20. Index on logistics.stock_level(product_id, warehouse_id)

Quick check of minimum stock threshold for a product in a warehouse (like for auto-order or low stock alerts). This index is needed for comparing with inventory.

21. Index on logistics.inventory_movement(product_id, changed_at DESC)

Lets you quickly get product movement history (audit) for recent periods—useful for error prevention, loss analysis, and supply control.

22. Index on logistics.transfer(product_id, requested_at DESC)

For analyzing logistics of transfers between warehouses, filtering by product and sorting by request date.

23. Index on logistics.shipping_rate(shipping_method_id, destination_zone)

When calculating delivery cost, you often pick a rate by method id and destination zone. The index speeds up calculations for the client at checkout.

24. Index on "order".order(user_id, placed_at DESC)

All user order history queries use filtering by user_id and sorting by order date. Composite index ensures fast order history for the user account.

25. Index on "order".order(status, placed_at)

For analytics and order reports by period, and for status search (like "processing"/"completed").

26. Index on "order".order_item(order_id)

Fetching all order items by order id—one of the most common operations for order details.

27. Index on "order".order_item(product_id)

Sales analytics and product stats need fast order item selections by product id.

28. Index on "order".return(order_id)

Linking returns to orders is used for support and return analytics. The index speeds up finding returns by order number.

29. Index on "order".cancellation(order_id)

Same as returns—speeds up finding order cancellations for analytics and support.

30. Index on "order".cart(user_id, updated_at DESC)

To find the latest user carts (like searching for "abandoned" carts), it's handy to have an index by user_id with sorting by last update date.

31. Index on payment.payment_transaction(order_id)

Most payment history queries filter by specific order. The index gives instant access to order transactions.

32. Index on payment.refund(transaction_id)

Lets you efficiently find refunds by specific transaction for support, reporting, and fraud control.

33. Index on payment.wallet(user_id)

Fast access to the user's wallet for balance and transaction history checks.

34. Index on payment.wallet_transaction(wallet_id, created_at DESC)

Selection by user wallet transactions sorted by date (like showing transaction history).

35. Index on support.support_ticket(user_id, created_at DESC)

History of a specific user's support tickets (user account/client service). Composite index optimizes these selections.

36. Index on support.ticket_message(ticket_id, sent_at)

To show the whole conversation for a ticket, it's handy to have an index by ticket and date—this speeds up message sorting by time.

37. Index on support.ticket_sla_tracking(ticket_id)

For SLA analytics and control by each ticket, fast access to SLA data is achieved by indexing ticket_id.

38. Index on marketing.promo_usage(user_id, used_at DESC)

For analyzing user activity by promo codes (analytics and abuse protection), you need fast search by user_id with sorting by time.

39. Index on analytics.product_view(user_id, viewed_at DESC)

Storing and analyzing product view history by user (personalization, recommendations) needs fast access by user_id with sorting by view time.

40. Index on analytics.search_query_log(query_text)

Popular queries and their usage frequency—a key search analytics tool. The index speeds up aggregations and counts by query text.

Note

For text searches with ILIKE, it's recommended to use GIN indexes with the pg_trgm extension, which are efficient for substring and fuzzy search. For large tables with date aggregation or sorting, use a DESC index on the date—it speeds up fetching the latest records.

It makes sense to tune indexes according to real execution plans and load stats, but the indexes above cover the main production query scenarios for our marketplace.

Adding Functions

Not tired yet? Then let's write a few more functions to make writing our current and future queries easier. Basically, to speed up key queries, reduce code duplication in the app, and centralize business logic on the database side.

1. Product search by keyword with tags and brands

Why you need it:

Plain search by name and description is limited. You often need to search by tags and brands too. A universal function centralizes advanced search logic, reduces code duplication, and makes frontend integration easier.

2. Get full product card by ID (all data for the card)

Why you need it:

The frontend often needs all product info at once: main fields, brand, category, images, tags, attributes, average rating, and review count. The function builds the full product card in one call, cutting down DB requests.

3. Get category hierarchy with nesting

Why you need it:

Building a category tree (or path) is needed for the storefront, filters, and breadcrumbs. Instead of recursive queries in client code, the function returns the whole hierarchy at once.

4. Calculate average price and min/max by category

Why you need it:

For catalog filters and analytics, it's handy to get aggregated stats for products in a category: price range, average value. The function saves you from repeating subqueries.

5. Check and auto-calculate product stock across all warehouses

Why you need it:

Lets you instantly know the total stock for a product (and each variant), which is useful for the storefront, warehouse, and logistics. Centralizes calculation, preventing business logic duplication.

6. Get user order history with details

Why you need it:

The function returns the user's order list, including order items, amounts, statuses, letting the frontend get the history in one call and build the user account right away.

7. Get user's average rating as seller/buyer

Why you need it:

To show user trust and reputation on the platform, it's important to know their average rating as a seller or buyer. The function does the aggregate calculation.

8. User promo code usage (validator with all conditions)

Why you need it:

All business logic for checking and applying a promo code (active, limits, date, etc.) is centralized in one function. This simplifies app logic and protects against errors from duplicated conditions.

9. Universal user event logging function

Why you need it:

For end-to-end analytics and audit, centralized event logging reduces code duplication and the risk of losing user action data.

10. Function to get bonus wallet balance and total accruals

Why you need it:

One call lets you get the user's current balance and total wallet accruals. Handy for dashboards and reduces SQL queries.

11. Universal order status change function with logging

Why you need it:

Changes order status, adds a record to the status history log, and minimizes errors when changing statuses in different app parts.

12. Get all support dialog messages (ticket + all messages)

Why you need it:

The function returns the whole ticket conversation, including ticket details and every message. Makes building the ticket history on the frontend easier.

13. Check if user exists by email or phone

Why you need it:

Used for registration and password recovery, prevents logic duplication on frontend and backend.

Note

This set of functions covers key business scenarios, makes working with data easier, optimizes logic, and speeds up frontend and integration development. Hope you liked it :)

Files with the solution