-- Offset-based pagination (works with Retool's table component)
SELECT *
FROM your_table
ORDER BY created_at DESC
LIMIT {{table1.pageSize}}
OFFSET {{table1.paginationOffset}}

-- Case-insensitive search across multiple columns
SELECT *
FROM your_table
WHERE (
  {{searchInput.value}} = ''
  OR name ILIKE '%' || {{searchInput.value}} || '%'
  OR email ILIKE '%' || {{searchInput.value}} || '%'
  OR id::text = {{searchInput.value}}
)

-- Filter by date range picker component
SELECT *
FROM your_table
WHERE created_at BETWEEN {{dateRange.start}} AND {{dateRange.end}}

-- Dashboard statistics with conditional aggregation
SELECT
  COUNT(*) as total,
  COUNT(*) FILTER (WHERE status = 'active') as active_count,
  COUNT(*) FILTER (WHERE created_at >= CURRENT_DATE) as today_count,
  ROUND(AVG(some_metric), 2) as avg_metric
FROM your_table
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'

-- Insert or update based on a unique key
INSERT INTO your_table (unique_key, name, value, updated_at)
VALUES ({{form.data.key}}, {{form.data.name}}, {{form.data.value}}, NOW())
ON CONFLICT (unique_key) DO UPDATE SET
  name = EXCLUDED.name,
  value = EXCLUDED.value,
  updated_at = NOW()

-- Soft delete with audit trail
UPDATE your_table
SET
  status = 'deleted',
  deleted_at = NOW(),
  deleted_by = {{current_user.id}}
WHERE id = {{table1.selectedRow.id}}

-- Log an action to an audit trail
INSERT INTO audit_logs (user_id, action, resource_type, resource_id, metadata, ip_address, created_at)
VALUES (
  {{current_user.id}},
  'update',
  'order',
  {{table1.selectedRow.id}},
  jsonb_build_object('old_status', {{table1.selectedRow.status}}, 'new_status', {{statusSelect.value}}),
  {{current_user.ip}},
  NOW()
)

-- Bulk update using selected rows from a table
UPDATE your_table
SET status = {{statusSelect.value}}, updated_at = NOW()
WHERE id = ANY(ARRAY[{{table1.selectedRows.map(r => r.id).join(',')}}]::int[])

-- Generate time-series data for chart components
SELECT
  DATE(created_at) as date,
  COUNT(*) as count,
  SUM(amount) as total_amount
FROM your_table
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE(created_at)
ORDER BY date