w3resource

Aggregate Sales Data from 2022 and 2023 Partitions


Retrieve Data from Multiple Partitions

Write a PostgreSQL query to fetch sales records from 2022 and 2023 in a range-partitioned Sales table.

Solution:

-- Fetch sales records from 2022 and 2023.
SELECT * FROM Sales 
-- Filter records with sale_date from January 1, 2022 up to but not including January 1, 2024.
WHERE sale_date >= '2022-01-01' AND sale_date < '2024-01-01';

Explanation:

  • Purpose of the Query:
    • Retrieves data from multiple partitions (Sales_2022 and Sales_2023).
  • Key Components:
    • The WHERE clause allows partition pruning.
  • Real-World Application:
    • Useful for analyzing data trends across multiple years.

Notes:

  • Ensure that the date range aligns with the partitioning strategy.

For more Practice: Solve these Related Problems:

  • Write a PostgreSQL query to retrieve data from two adjacent partitions using a BETWEEN clause on the partition key.
  • Write a PostgreSQL query to fetch data from non-contiguous partitions by using an IN clause with specific partition key values.
  • Write a PostgreSQL query to combine data from multiple partitions using UNION ALL while applying different filters to each partition.
  • Write a PostgreSQL query to aggregate data across multiple partitions using GROUP BY and a date range that spans them.


Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous PostgreSQL Exercise: Query Data using Partition Pruning.

Next PostgreSQL Exercise: Count Records in Each Partition.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.