---
title: Common Queries
slug: redshift/-AAL-common-queries
icon: {"lucideIcon":"ScanSearch"}
docTags: 
createdAt: 2026-10-01T12:37:49.897Z
---

### Current stock on hand

```sql
SELECT
    p.name,
    p.category,
    ps.stock       AS units_on_hand,
    ps.sub_stock   AS subunits_on_hand,
    p.minimum_stock,
    p.optimum_stock
FROM product_stocks ps
JOIN products p ON p.id = ps.product_id
WHERE ps.deleted_at IS NULL
  AND p.deleted_at IS NULL
ORDER BY p.name;
```

### Reorder list — below minimum stock

```sql
SELECT
    p.name,
    ps.stock       AS units_on_hand,
    p.minimum_stock,
    p.optimum_stock,
    p.optimum_stock - ps.stock AS suggested_reorder_qty
FROM product_stocks ps
JOIN products p ON p.id = ps.product_id
WHERE ps.deleted_at IS NULL
  AND p.deleted_at IS NULL
  AND ps.stock < p.minimum_stock
ORDER BY (p.minimum_stock - ps.stock) DESC;
```

### Products nearing expiry (via sold service package products)

This only surfaces expiry data for products consumed as part of a bundled service, since batch-level expiry on directly-sold products isn't exposed (see [Stock & Services](#)):

```sql
SELECT
    sspp.name,
    sspp.batch_no,
    sspp.expiration_date,
    sspp.quantity
FROM sold_service_package_products sspp
WHERE sspp.deleted_at IS NULL
  AND sspp.expiration_date IS NOT NULL
  AND sspp.expiration_date <= DATEADD(day, 30, CURRENT_DATE)
ORDER BY sspp.expiration_date;
```

### Service package revenue

```sql
SELECT
    ap.name                                    AS service_name,
    COUNT(*)                                   AS times_sold,
    CAST(SUM(ssp.total) AS NUMERIC(18,2))      AS total_revenue
FROM sold_service_packages ssp
JOIN service_packages sp   ON sp.id = ssp.service_package_id
JOIN available_services ap ON ap.id = sp.service_id
WHERE ssp.deleted_at IS NULL
GROUP BY ap.name
ORDER BY total_revenue DESC;
```