Efficient Pagination with Total Count for Datatables

SELECT
  id,
  name,
  created_at,
  COUNT(*) OVER() AS total_records
FROM products
ORDER BY created_at DESC
OFFSET ? LIMIT ?;

How it works: This SQL query demonstrates an efficient way to paginate data while also retrieving the total count of records without needing a separate `COUNT(*)` query. It uses `OFFSET` and `LIMIT` (or `ROW_NUMBER()` for older SQL Server versions) for pagination, and the `COUNT(*) OVER()` window function to include the total number of rows in the entire result set, before pagination is applied. This is particularly useful for frontend components that display both paginated data and the overall record count.