Build it

We will load a month of practice data, then write four real reports and call one from NestJS through Prisma. To try the core patterns without installing anything, use the exercises in the Practice section, which run in your browser.

1. Load practice data

   Run this on the tables from Modeling data in tables. It creates 8 sites, 40 products and 30 days of receipts. Low article ids sell more, so rankings look realistic. One store loses receipts in the last 3 days so the comparison report has something to find. Prices are in cents.

   SQLCopy

   INSERT INTO site (site_code, name, format, city) VALUES
     ('S001', 'Riverside', 'STORE', 'Bayside'),     ('S002', 'Hill Road', 'STORE', 'Bayside'),
     ('S003', 'Market Square', 'STORE', 'Bayside'), ('S004', 'Station Road', 'STORE', 'Bayside'),
     ('S005', 'Old Town', 'STORE', 'Hilltown'),     ('S006', 'Lakeview', 'STORE', 'Riverton'),
     ('P001', 'Ravi''s Corner Shop', 'PARTNER', 'Bayside'), ('DC01', 'North DC', 'DC', 'Bayside');
   INSERT INTO category (name) VALUES ('Grocery'), ('Fresh'), ('Personal care');
   INSERT INTO article (sku, name, category_id, unit_price_cents)
   SELECT 'SKU' || lpad(g::text, 5, '0'), 'Article ' || g, 1 + g % 3,
          50 + floor(random() * 1950)::int                -- 50 to 1999 cents
   FROM generate_series(1, 40) AS g;
   
   -- 300 receipts per day per selling site, 30 days
   INSERT INTO sale (site_id, local_txn_id, sale_date, sold_at, total_cents, pay_method)
   SELECT s.id, s.site_code || '-' || to_char(current_date - d, 'YYMMDD') || '-' || n,
          current_date - d,
          (current_date - d + time '09:00' + random() * interval '13 hours') AT TIME ZONE 'Asia/Dhaka',
          0, (ARRAY['CASH','CARD','WALLET'])[1 + floor(random() * 3)::int]::pay_method
   FROM site s, generate_series(0, 29) AS d, generate_series(1, 300) AS n
   WHERE s.format <> 'DC';

   SQLCopy

   -- 1 to 4 lines per receipt; power(random(), 2) favours low article ids
   INSERT INTO sale_line (sale_id, line_no, article_id, qty, unit_price_cents)
   SELECT s.id, l.n, a.id, l.qty, a.unit_price_cents
   FROM sale s
   CROSS JOIN LATERAL (
     SELECT n, 1 + floor(power(random(), 2) * 40)::int AS article_id,
               1 + floor(random() * 3)::int AS qty
     FROM generate_series(1, 1 + (s.id % 4)::int) AS n
   ) AS l
   JOIN article a ON a.id = l.article_id;
   
   UPDATE sale s SET total_cents = t.amount
   FROM (SELECT sale_id, sum(qty * unit_price_cents) AS amount FROM sale_line GROUP BY sale_id) AS t
   WHERE t.sale_id = s.id;
   
   INSERT INTO stock (site_id, article_id, qty)
   SELECT s.id, a.id, floor(random() * 200)::int FROM site s CROSS JOIN article a;
   
   DELETE FROM sale                       -- Station Road's POS "lost" receipts lately
   WHERE site_id = (SELECT id FROM site WHERE site_code = 'S004')
     AND sale_date > current_date - 3 AND random() < 0.6;
   ANALYZE;                               -- refresh planner statistics

   ANALYZE refreshes the statistics the planner uses to choose a plan; Reading query plans and fixing slow queries shows why that matters.

2. Top 5 products per store, last 7 days

   The first CTE sums units per store and product. The second ranks them inside each store. The final SELECT keeps the top five and adds names.

   SQLCopy

   WITH sold AS (
     SELECT s.site_id, sl.article_id, sum(sl.qty) AS units
     FROM sale s
     JOIN sale_line sl ON sl.sale_id = s.id
     WHERE s.sale_date >= current_date - 7
     GROUP BY s.site_id, sl.article_id
   ), ranked AS (
     SELECT site_id, article_id, units,
            row_number() OVER (PARTITION BY site_id
                               ORDER BY units DESC, article_id) AS rn
     FROM sold
   )
   SELECT si.site_code, a.name AS article, r.units, r.rn
   FROM ranked r
   JOIN site si   ON si.id = r.site_id
   JOIN article a ON a.id = r.article_id
   WHERE r.rn <= 5
   ORDER BY si.site_code, r.rn;

   OutputCopy

    site_code |  article  | units | rn
   -----------+-----------+-------+----
    P001      | Article 1 |  1891 |  1
    P001      | Article 2 |   800 |  2
    P001      | Article 3 |   665 |  3
    ...

3. Daily sales with day-over-day change

   lag() reads the previous day's total. Note the nesting: sum(total_cents) is the group aggregate, and lag(…) OVER runs on top of the grouped rows. The ::numeric cast keeps the fraction in the percentage.

   SQLCopy

   SELECT sale_date,
          sum(total_cents) AS revenue_cents,
          sum(total_cents) - lag(sum(total_cents)) OVER (ORDER BY sale_date) AS change_cents,
          round(100.0 * (sum(total_cents)::numeric
                         / lag(sum(total_cents)) OVER (ORDER BY sale_date) - 1), 1) AS change_pct
   FROM sale
   WHERE site_id = (SELECT id FROM site WHERE site_code = 'S004')
     AND sale_date >= current_date - 6
   GROUP BY sale_date
   ORDER BY sale_date;

   OutputCopy

    sale_date  | revenue_cents | change_cents | change_pct
   ------------+---------------+--------------+------------
    2026-10-10 |       1587933 |              |
    2026-10-11 |       1708481 |       120548 |        7.6
    2026-10-12 |       1604104 |      -104377 |       -6.1
    2026-10-13 |       1641566 |        37462 |        2.3
    2026-10-14 |        625247 |     -1016319 |      -61.9   <- the POS problem starts
    2026-10-15 |        668784 |        43537 |        7.0

   Your numbers differ because the data is random. 1587933 cents is $15,879.33. The first row has no previous day, so lag gives NULL. To get month-to-date instead, use sum(sum(total_cents)) OVER (ORDER BY sale_date).

4. Stores selling below last week

   Two CTEs, one per week, then a comparison. The LEFT JOIN plus coalesce keeps a store that sold nothing this week, which is the most alarming case.

   SQLCopy

   WITH this_week AS (
     SELECT site_id, sum(total_cents) AS revenue_cents
     FROM sale
     WHERE sale_date >= current_date - 6                           -- last 7 days
     GROUP BY site_id
   ), last_week AS (
     SELECT site_id, sum(total_cents) AS revenue_cents
     FROM sale
     WHERE sale_date >= current_date - 13 AND sale_date < current_date - 6
     GROUP BY site_id
   )
   SELECT si.site_code, si.name,
          lw.revenue_cents               AS last_week,
          coalesce(tw.revenue_cents, 0)  AS this_week,
          round(100.0 * (coalesce(tw.revenue_cents, 0)::numeric / lw.revenue_cents - 1), 1)
            AS change_pct
   FROM last_week lw
   JOIN site si           ON si.id = lw.site_id
   LEFT JOIN this_week tw ON tw.site_id = lw.site_id
   WHERE coalesce(tw.revenue_cents, 0) < lw.revenue_cents * 0.9      -- down more than 10%
   ORDER BY change_pct;

   OutputCopy

    site_code |     name     | last_week | this_week | change_pct
   -----------+--------------+-----------+-----------+------------
    S004      | Station Road |  11466371 |   8545846 |      -25.5

5. Prove the dead-stock query works

   A query that returns nothing may be correct, or may be broken. Plant a row you expect it to find, run it, and undo everything with ROLLBACK, so your practice data stays clean. Paste the dead-stock query from the EXISTS section where marked.

   SQLCopy

   BEGIN;
   -- Two new products arrive at Station Road but have not sold yet
   INSERT INTO article (sku, name, category_id, unit_price_cents)
   VALUES ('SKU00041', 'Coconut oil 1L', 1, 450),
          ('SKU00042', 'Basmati rice 1kg', 1, 290);
   INSERT INTO stock (site_id, article_id, qty)
   SELECT s.id, a.id, 24
   FROM site s, article a
   WHERE s.site_code = 'S004' AND a.sku IN ('SKU00041', 'SKU00042');
   
   -- ...paste the dead-stock query here...
   
   ROLLBACK;   -- the test rows disappear

   OutputCopy

      sku    |       name       | qty
   ----------+------------------+-----
    SKU00041 | Coconut oil 1L   |  24
    SKU00042 | Basmati rice 1kg |  24

   Both planted products appear, and no real product does, because every real one sold in the last week. Now you trust the query. The same trick works for every report: before you rely on "no stores are below target", plant one that is.

6. Upsert stock after a delivery

   When goods arrive (a goods received note, or GRN), add the quantity if the row exists, or create it if this site never stocked the product. RETURNING sends back the new row, so the app needs no second query.

   SQLCopy

   INSERT INTO stock (site_id, article_id, qty)
   VALUES (1, 7, 24)
   ON CONFLICT (site_id, article_id)                 -- the primary key is the referee
   DO UPDATE SET qty        = stock.qty + EXCLUDED.qty,   -- EXCLUDED = the row we tried
                 updated_at = now()
   RETURNING site_id, article_id, qty;
   
   -- A retried receipt upload: the second attempt inserts nothing
   INSERT INTO sale (site_id, local_txn_id, sale_date, total_cents, pay_method)
   VALUES (1, 'S001-261016-1', current_date, 4500, 'WALLET')
   ON CONFLICT (site_id, local_txn_id) DO NOTHING
   RETURNING id;                                     -- zero rows means "already had it"

   In Prisma, prisma.stock.upsert() covers the simple cases. For a single unique key with simple data, recent Prisma versions send it as one ON CONFLICT statement; in other cases it reads first and then writes. When the exact SQL matters, write it yourself with $executeRaw.

7. Call the report from NestJS with $queryRaw

   Prisma's $queryRaw is a tagged template. Each ${value} is sent to Postgres as a separate parameter, never pasted into the SQL text, so it is safe from SQL injection. The generic gives the result a type. Postgres bigint (which is what sum() of an integer column and row_number() return) arrives as JavaScript bigint.

   TypeScriptCopy

   import { Injectable } from '@nestjs/common';
   import { PrismaService } from '../prisma/prisma.service';
   
   type TopArticle = {
     site_code: string;
     sku: string;
     name: string;
     units: bigint;          // sum(integer) is bigint in Postgres
     revenue_cents: bigint;  // also a sum of integers: exact, and a bigint
     rn: bigint;             // row_number() is bigint
   };
   
   @Injectable()
   export class ReportsService {
     constructor(private readonly prisma: PrismaService) {}
   
     topArticles(siteCodes: string[], days: number, top = 5): Promise<TopArticle[]> {
       return this.prisma.$queryRaw<TopArticle[]>`
         WITH sold AS (
           SELECT s.site_id, sl.article_id,
                  sum(sl.qty) AS units, sum(sl.qty * sl.unit_price_cents) AS revenue_cents
           FROM sale s
           JOIN sale_line sl ON sl.sale_id = s.id
           JOIN site si      ON si.id = s.site_id
           WHERE si.site_code = ANY(${siteCodes})          -- array becomes one parameter
             AND s.sale_date >= current_date - ${days}::int
           GROUP BY s.site_id, sl.article_id
         ), ranked AS (
           SELECT *, row_number() OVER (PARTITION BY site_id ORDER BY units DESC) AS rn
           FROM sold
         )
         SELECT si.site_code, a.sku, a.name, r.units, r.revenue_cents, r.rn
         FROM ranked r
         JOIN site si   ON si.id = r.site_id
         JOIN article a ON a.id = r.article_id
         WHERE r.rn <= ${top}
         ORDER BY si.site_code, r.rn`;
     }
   }

   The type is a promise Prisma does not check at runtime. If you rename a column in the SQL, update the type too. Convert bigint values to numbers (safe below 2⁵³) or strings in the controller's response DTO before sending JSON.

8. See why $queryRawUnsafe is dangerous

   $queryRawUnsafe takes a plain string. If you build that string with a template literal, user input becomes part of the SQL.

   TypeScriptCopy

   const input = "S001' OR '1'='1";   // typed into a search box
   
   await prisma.$queryRaw`SELECT count(*) FROM site WHERE site_code = ${input}`;
   // -> 0 rows matched: the whole string was treated as one site code
   
   await prisma.$queryRawUnsafe(`SELECT count(*) FROM site WHERE site_code = '${input}'`);
   // -> all 8 rows matched: the input rewrote the WHERE clause
   
   await prisma.$queryRawUnsafe('SELECT count(*) FROM site WHERE site_code = $1', input);
   // -> 0 rows: still safe, because the value is passed as a separate $1 parameter

   If you need a dynamic column or table name, which parameters cannot provide, pick it from a fixed allow-list in code. Never pass it through from the request.