DEV Community

Cristian Sifuentes
Cristian Sifuentes

Posted on • Edited on

SQL Deep‑Dive: Building a **“One‑Row Interaction Dossier”** – From CTEs to Satellite Counts

#sql #sqlserver #database #programming

SQL Deep‑Dive: Building a **“One‑Row Interaction Dossier”** – From CTEs to Satellite Counts

SQL Deep‑Dive: Building a “One‑Row Interaction Dossier” – From CTEs to Satellite Counts

Modern contact‑center schemas can feel like Greek tragedy: one Interactions table at the center, surrounded by a chorus of logs, callbacks, flows, agents, recordings… and every FK conflict is another dramatic twist.

In this post we’ll craft a single, surgical query that answers the perennial questions:

  • How many child rows exist for this interaction?
  • Which satellites are missing before I DELETE?
  • Where might a NO_ACTION FK blow up my purge job?

You’ll walk away with a pattern you can adapt to any hub‑and‑spoke model, plus a cheat‑sheet of T‑SQL techniques worthy of your next database‑forensics session.

1 · Schema Recap – Why We Need a Dossier 

dbo.Interactions                 -- hub (PK = Id)
├── dbo.AACalls                  -- core child w/ AutoAttendantId filter ⭐
│   └── dbo.AACallsLog
├── dbo.InteractionFinalStatus
├── dbo.InteractionsAgents
│   └── dbo.InteractionsAgentsLog
├── dbo.InteractionsCallbacks
│   └── dbo.InteractionsCallbacksLog
├── dbo.InteractionsFlows
│   └── dbo.InteractionsFlowsLog
└── dbo.Recordings
    └── dbo.RecordingsLog
Enter fullscreen mode Exit fullscreen mode

Goal: For one interaction, return a single summary row showing how many records exist in each satellite table—even if some are zero.

2 · The Code (Copy‑Paste Ready) 

/****************************************************************************************
  Deep‑dive snapshot for a single Interaction and all its child entities
****************************************************************************************/
DECLARE @TenantId           INT      = 1;      -- 🔧 set as needed
DECLARE @AutoAttendantId    INT      = 42;     -- 🔧 AA filter
DECLARE @InteractionsTypeId INT      = 3;      -- 🔧 type filter
DECLARE @InteractionId      BIGINT   = 100001; -- 🔧 the star of the show

;WITH base AS (  -- 🎬 establish the hub in a CTE
    SELECT i.Id
    FROM dbo.Interactions AS i
    WHERE i.TenantId           = @TenantId
      AND i.InteractionsTypeId = @InteractionsTypeId
      AND i.Id                 = @InteractionId
)
SELECT
    b.Id                                   AS InteractionId,

    /*—— core AA call (must exist) ——*/
    COUNT(DISTINCT ac.Id)                  AS AACallsCount,

    /*—— optional logs & satellites ——*/
    COUNT(DISTINCT acl.Id)                 AS AACallsLogCount,
    COUNT(DISTINCT ifs.Id)                 AS FinalStatusCount,
    COUNT(DISTINCT ia.Id)                  AS AgentsCount,
    COUNT(DISTINCT ial.Id)                 AS AgentsLogCount,
    COUNT(DISTINCT icb.Id)                 AS CallbacksCount,
    COUNT(DISTINCT icbl.Id)                AS CallbacksLogCount,
    COUNT(DISTINCT ifl.Id)                 AS FlowsCount,
    COUNT(DISTINCT ifll.Id)                AS FlowsLogCount,
    COUNT(DISTINCT r.Id)                   AS RecordingsCount,
    COUNT(DISTINCT rl.Id)                  AS RecordingsLogCount
FROM base                                    AS b

/*---------------------------------------------------------
  INNER JOIN ➜ enforce AA filter (interaction **must** appear
  in AACalls with the chosen AutoAttendantId)
---------------------------------------------------------*/
INNER JOIN dbo.AACalls                AS ac   
  ON ac.InteractionId = b.Id 
 AND ac.AutoAttendantId = @AutoAttendantId

/*---------------------------------------------------------
  LEFT JOINs ➜ keep hub row even when satellites are empty
---------------------------------------------------------*/
LEFT  JOIN dbo.AACallsLog               AS acl   ON acl.InteractionId  = b.Id
LEFT  JOIN dbo.InteractionFinalStatus   AS ifs   ON ifs.InteractionId  = b.Id
LEFT  JOIN dbo.InteractionsAgents       AS ia    ON ia.InteractionId   = b.Id
LEFT  JOIN dbo.InteractionsAgentsLog    AS ial   ON ial.InteractionId  = b.Id
LEFT  JOIN dbo.InteractionsCallbacks    AS icb   ON icb.InteractionId  = b.Id
LEFT  JOIN dbo.InteractionsCallbacksLog AS icbl  ON icbl.InteractionId = b.Id
LEFT  JOIN dbo.InteractionsFlows        AS ifl   ON ifl.InteractionId  = b.Id
LEFT  JOIN dbo.InteractionsFlowsLog     AS ifll  ON ifll.InteractionId = b.Id
LEFT  JOIN dbo.Recordings               AS r     ON r.InteractionId    = b.Id
LEFT  JOIN dbo.RecordingsLog            AS rl    ON rl.InteractionId   = b.Id
GROUP BY
    b.Id;
GO
Enter fullscreen mode Exit fullscreen mode

3 · Why This Query Packs a Punch

Feature Purpose 💡 Pro Tip
CTE (base) Isolates the exact interaction once, then drives every join. Swap for a list table to audit many interactions.
INNER JOIN on AACalls Enforces the AutoAttendant filter; missing rows → query returns 0 rows, surfacing data bugs fast. Flip to LEFT JOIN if you want “zero calls” interactions too.
LEFT JOIN for satellites Keeps the hub row even when logs are missing, so counts are 0 instead of NULL (or lost rows). Great for orphan‑detection before DELETE.
COUNT(DISTINCT …) Delivers a clean numeric dashboard in one row—ready for Power BI or Grafana. Replace with MAX(CASE WHEN …) if you need booleans.
Comment blocks Make intent obvious for the next maintainer (a.k.a. future you). ESLint but for SQL? Comments matter!

4 · Common Adaptations

Need Quick Change
Get full row data Drop the COUNT aggregates, select ac.*, ia.*, etc., and remove the GROUP BY.
Bulk audit Replace scalar variables with table‑valued parameters and keep the CTE.
Performance tuning Add covering indexes on each satellite’s InteractionId (and AutoAttendantId for AACalls).
Detach AA filter Move the filter into the WHERE clause and convert INNER → LEFT JOIN to include unmatched calls.

5 · Takeaways

  • Hub‑and‑spoke schemas thrive on CTEs – they give you one hub reference to rule them all.
  • Mixing INNER and LEFT joins deliberately reveals empty satellites without dropping the base row.
  • Aggregated counts turn a noisy 1‑to‑many jungle into a dashboard you can eyeball in 200 ms.

Next time a colleague asks “Why can’t we delete this interaction?”, run the dossier, point at the non‑zero counts, and watch the lightbulb go on.

✍️ Written by: Cristian Sifuentes – Full-stack dev crafting scalable apps with [NET - Azure], [Angular - React], Git, SQL & extensions. Clean code, dark themes, atomic commits

Happy querying, and may your FKs always align! ⚡📈

Top comments (0)