Definition

The mkb_drugpack model provides pharmaceutical packaging details, linking medications to their commercial forms. It combines preparation data with pack configurations including pricing, quantities, and supplier information.

Constraints and Notes

• Soft deletion : When is_deleted is true, data fields may be nullified in upstream transformations and should be excluded from most analytical queries. Primary keys and foreign keys are preserved so deleted linked records can be identified

Examples

Detailed packaging information for a specific drug

SELECT
  drug.term AS drug_name,
  drug.dmd_product_code_id,
  pack.pack_description,
  pack.units_per_pack,
  pack.unit_of_measure,
  pack.pack_price,
  pack.price_per_unit
FROM hive.explorer_open_safely.drug_code AS drug
JOIN hive.explorer_open_safely.mkb_drug_pack AS pack
  ON drug.code_id = pack.preparation_code_id
WHERE
  drug.term LIKE '%metformin%'
ORDER BY
  drug.term, pack.units_per_pack

All packaging options for a specific medication

SELECT
  term AS drug_name,
  supplier_name,
  pack_description,
  units_per_pack,
  unit_of_measure,
  pack_price,
  price_per_unit
FROM hive.explorer_open_safely.mkb_drug_pack
WHERE
  term LIKE '%Amoxicillin%'
  AND is_deleted = FALSE
ORDER BY
  price_per_unit ASC

Medications with the most packaging options

SELECT
  term AS drug_name,
  COUNT(DISTINCT preparation_pack_id) AS pack_options_count,
  COUNT(DISTINCT supplier_name) AS supplier_count,
  MIN(price_per_unit) AS min_price_per_unit,
  MAX(price_per_unit) AS max_price_per_unit,
  AVG(price_per_unit) AS avg_price_per_unit
FROM hive.explorer_open_safely.mkb_drug_pack
WHERE
  is_deleted = FALSE
  AND price_per_unit > 0
GROUP BY
  term
HAVING
  COUNT(DISTINCT preparation_pack_id) > 3
ORDER BY
  pack_options_count DESC,
  supplier_count DESC
LIMIT 20