SQL इंटरव्यू: सटीक Day-7 कोहोर्ट रिटेंशन की गणना करें

प्रॉम्प्ट और लागू संदर्भ

आपको दो PostgreSQL टेबल्स दिए गए हैं:

CREATE TABLE users (
  user_id bigint PRIMARY KEY,
  signup_at timestamptz NOT NULL,
  acquisition_channel text
);

CREATE TABLE events (
  event_id bigint PRIMARY KEY,
  user_id bigint NOT NULL,
  event_at timestamptz NOT NULL,
  event_name text NOT NULL
);

उन यूज़र्स के लिए जिनकी साइनअप तिथि 1 जनवरी से 31 जनवरी, 2026 के बीच है, साइनअप तिथि और एक्विजिशन चैनल द्वारा समूहीकृत (grouped) सटीक Day-7 रिटेंशन की रिपोर्ट करें। बिज़नेस टाइम ज़ोन America/New_York है। Day 0 यूज़र की स्थानीय साइनअप तिथि है। एक यूज़र को Day 7 पर रिटेन माना जाता है यदि उनके पास स्थानीय कैलेंडर तिथि signup_day + 7 पर कम से कम एक core_action_completed इवेंट हो। Days 1–6, Day 8, या किसी भी बाद की तारीख के इवेंट्स इस परिभाषा को पूरा नहीं करते हैं।

cohort_day, acquisition_channel, cohort_size, retained_users, और d7_retention_rate लौटाएं। कई क्वालिफ़ाइंग इवेंट्स को प्रति यूज़र एक बार गिना जाता है। बिना किसी रिटर्न इवेंट वाले यूज़र्स को डिनोमिनेटर में रहना चाहिए। Null चैनल्स को unknown के रूप में समूहीकृत किया जाता है।

data_complete_through = '2026-02-01 05:00:00+00' मान लें। यह एक एक्सक्लूसिव इंजेशन वॉटरमार्क है और न्यूयॉर्क में 1 फरवरी की शुरुआत में मध्यरात्रि के बराबर है। किसी साइनअप कोहोर्ट को केवल तभी शामिल करें जब उसकी पूरी Day-7 कैलेंडर तिथि उस वॉटरमार्क से पहले हो। 24 जनवरी का कोहोर्ट परिपक्व (mature) है क्योंकि इसका Day 7, 31 जनवरी है; 25 जनवरी का कोहोर्ट नहीं है।

यह समस्या एनालिटिक्स-इंजीनियर, डेटा-एनालिस्ट और प्रोडक्ट-एनालिटिक्स इंटरव्यू में उपयोगी है क्योंकि कठिन हिस्सा मेट्रिक कॉन्ट्रैक्ट है, न कि डिवीज़न। क्वेरी को किसी भी चीज़ को एग्रीगेट करने से पहले कोहोर्ट ग्रेन, रिटर्न विंडो, टाइम ज़ोन, डेटा पूर्णता और सेगमेंटेशन को संरेखित (align) करना होगा।

इंटरव्यूअर क्या मूल्यांकन करता है

पहला संकेत यह है कि क्या उम्मीदवार पूछता है कि "Day-7 रिटेंशन" का क्या अर्थ है। तीन मेट्रिक्स जो समान लगते हैं वे भौतिक रूप से भिन्न हैं: ठीक Day 7 पर गतिविधि, Days 1–7 के दौरान किसी भी बिंदु पर गतिविधि, और Day 7 या उसके बाद गतिविधि। एक क्वेरी सिंटैक्टिकली सही हो सकती है और फिर भी गलत प्रश्न का उत्तर दे सकती है। इस प्रॉम्प्ट के लिए पहली परिभाषा की आवश्यकता है।

दूसरा संकेत डिनोमिनेटर का अनुशासन है। डिनोमिनेटर एक मैच्योर साइनअप कोहोर्ट में प्रत्येक योग्य यूज़र है। इवेंट्स टेबल से शुरू करना, या साइनअप में रिटर्न्स को इनर-जॉइन करना, उन यूज़र्स को चुपचाप हटा देता है जो कभी वापस नहीं आए और रिटेंशन को बढ़ा-चढ़ाकर पेश करता है। कोहोर्ट को पहले बनाया जाना चाहिए और लेफ्ट जॉइन के साथ संरक्षित किया जाना चाहिए।

तीसरा संकेत ग्रेन कंट्रोल है। आउटपुट में प्रति साइनअप तिथि और चैनल पर एक पंक्ति होती है, जबकि रिटेंशन फ़्लैग में प्रति यूज़र अधिकतम एक पंक्ति होती है। रॉ इवेंट डेटा में पुनः प्रयास (retries), डुप्लिकेट्स और उसी तिथि पर कई मान्य कार्रवाइयां शामिल हो सकती हैं। इवेंट पंक्तियों की गिनती करने से रिटेन किए गए यूज़र्स के बजाय एक्शन वॉल्यूम मापा जाएगा, इसलिए न्यूमरेटर को यूज़र स्तर पर डिडप्लिकेट किया जाना चाहिए।

चौथा संकेत टेम्पोरल शुद्धता है। एक स्थानीय कैलेंडर दिन हमेशा एक निश्चित 24-घंटे का अंतराल नहीं होता है और एक नामित ज़ोन डेलाइट-सेविंग नियमों के तहत UTC ऑफ़सेट बदल सकता है। पहले एक स्थानीय तिथि प्राप्त करना और नामित-ज़ोन की मध्यरात्रियों से प्रत्येक यूज़र की लक्षित-दिन की सीमाएँ बनाना अनुबंध को सीधे व्यक्त करता है। event_at = signup_at + INTERVAL '7 days' जैसा परीक्षण इसके बजाय बीते हुए समय (elapsed-time) के प्रश्न का उत्तर देता है।

अंतिम संकेत परिचालन निर्णय (operational judgment) है। हाल के कोहोर्ट्स को एक पूर्ण अवलोकन विंडो की आवश्यकता होती है, और दीवार घड़ी यह साबित नहीं करती है कि एक इवेंट पाइपलाइन पूर्ण है। एक उम्मीदवार को डेटा वॉटरमार्क का उपयोग करना चाहिए, देर से आने वाले इवेंट्स की व्याख्या करनी चाहिए, सबसे छोटे विश्वसनीय रिपोर्टिंग ग्रेन को मान्य करना चाहिए, और क्वेरी फ़्रीक्वेंसी और डेटा वॉल्यूम के अनुसार इंडेक्स या एक दैनिक गतिविधि टेबल चुनना चाहिए।

उत्तर देने से पहले स्पष्ट करने योग्य प्रश्न

• क्या Day 7 सटीक, सीमित (bounded), या रोलिंग है? यह उत्तर साइनअप के बाद सातवीं स्थानीय तिथि पर सटीक रूप से एक इवेंट का उपयोग करता है। "सात दिनों के भीतर" और "Day 7 पर या उसके बाद" के लिए अलग-अलग प्रेडिकेट्स की आवश्यकता होती है।
• कौन सा इवेंट रिटेंशन साबित करता है? प्रॉम्प्ट core_action_completed का उपयोग करता है। लॉगिन, पेज व्यू, खरीदारी, या कोई भी इवेंट अलग-अलग प्रोडक्ट अर्थ उत्पन्न करेगा और इसे लापरवाही से प्रतिस्थापित नहीं किया जाना चाहिए।
• एक दिन को क्या परिभाषित करता है? रिपोर्टिंग अनुबंध America/New_York कैलेंडर तिथियों का उपयोग करता है। प्रति-यूज़र टाइम ज़ोन या UTC कोहोर्ट सदस्यता और रिटर्न विंडो को बदल देगा।
• एक कोहोर्ट कब मैच्योर होता है? एक कोहोर्ट केवल तभी रिपोर्ट करने योग्य होता है जब एक्सक्लूसिव डेटा वॉटरमार्क उस कोहोर्ट के Day 7 के बाद अगली स्थानीय मध्यरात्रि पर या उससे आगे हो।
• देर से आने वाले इवेंट्स को कैसे संभाला जाता है? यदि वॉटरमार्क बाद में आगे बढ़ता है या बैकफ़िल किए गए इवेंट्स इससे पहले आते हैं, तो प्रभावित कोहोर्ट्स की पुनर्गणना की जानी चाहिए। प्रदर्शित दर को अपरिवर्तनीय नहीं माना जाना चाहिए।
• कौन सा चैनल मान लागू होता है? क्वेरी मानती है कि users.acquisition_channel साइनअप-समय का अपरिवर्तनीय एट्रिब्यूशन है। एक परिवर्तनशील वर्तमान चैनल के लिए इसके बजाय एक वर्ज़नयुक्त एट्रिब्यूशन स्नैपशॉट की आवश्यकता होती है।
• कोहोर्ट ग्रेन क्या है? यह प्रॉम्प्ट साइनअप तिथि और चैनल द्वारा समूहीकृत करता है। साप्ताहिक कोहोर्ट्स समान यूज़र-स्तरीय तर्क का उपयोग करते हैं लेकिन एक अलग अंतिम ग्रुपिंग कुंजी का उपयोग करते हैं।
• क्या दर एक भिन्न (fraction) या प्रतिशत होनी चाहिए? क्वेरी चार दशमलव स्थानों तक राउंड किया गया भिन्न लौटाती है: 0.3333 का अर्थ 33.33% है।

30-सेकंड उत्तर फ़्रेमवर्क

"मैं सहमत व्यावसायिक समय क्षेत्र में यूज़र की साइनअप कैलेंडर तिथि प्लस सात के रूप में सटीक Day 7 को परिभाषित करूँगा। पहले मैं स्थानीय-मध्यरात्रि UTC सीमाओं से जनवरी कोहोर्ट का निर्माण करता हूँ और साइनअप दिन और साइनअप-समय चैनल के साथ प्रति यूज़र एक पंक्ति संग्रहीत करता हूँ। फिर मैं उन कोहोर्ट्स को बाहर करता हूँ जिनका पूरा लक्षित दिन एक्सक्लूसिव डेटा वॉटरमार्क से परे है। प्रत्येक शेष यूज़र के लिए, मैं हाफ़-ओपन स्थानीय Day-7 अंतराल में लक्षित इवेंट की तलाश करता हूँ, प्रति यूज़र एक रिटेन की गई पंक्ति में डिडप्लिकेट करता हूँ, और उस फ़्लैग को मैच्योर कोहोर्ट में वापस लेफ्ट-जॉइन करता हूँ। अंत में मैं तिथि और चैनल द्वारा कोहोर्ट यूज़र्स और रिटेन किए गए यूज़र्स की गणना करता हूँ। मैं डुप्लिकेट्स, ज़ीरो रिटर्न्स, Day-6 और Day-8 इवेंट्स, स्थानीय मध्यरात्रियों, DST सीमाओं और मैच्योरिटी कटऑफ़ का परीक्षण करूँगा।"

चरण-दर-चरण विस्तृत विश्लेषण

उन मापदंडों से शुरू करें जो रिपोर्टिंग अनुबंध को दृश्यमान बनाते हैं। cohort_end एक्सक्लूसिव है, और वॉटरमार्क पहला अनऑब्जर्व्ड क्षण है। हाफ़-ओपन अंतराल मध्यरात्रि में किसी इवेंट की दोहरी गिनती से बचते हैं और आसन्न तिथियों में सुचारू रूप से बनते हैं।

WITH params AS (
  SELECT
    'America/New_York'::text AS tz,
    DATE '2026-01-01' AS cohort_start,
    DATE '2026-02-01' AS cohort_end,
    TIMESTAMPTZ '2026-02-01 05:00:00+00' AS data_complete_through
),
cohort AS (
  SELECT
    u.user_id,
    COALESCE(u.acquisition_channel, 'unknown') AS acquisition_channel,
    (u.signup_at AT TIME ZONE p.tz)::date AS signup_day
  FROM users AS u
  CROSS JOIN params AS p
  WHERE u.signup_at >= (p.cohort_start::timestamp AT TIME ZONE p.tz)
    AND u.signup_at < (p.cohort_end::timestamp AT TIME ZONE p.tz)
),
mature_cohort AS (
  SELECT c.*
  FROM cohort AS c
  CROSS JOIN params AS p
  WHERE c.signup_day + 7
    < (p.data_complete_through AT TIME ZONE p.tz)::date
),
retained AS (
  SELECT DISTINCT c.user_id
  FROM mature_cohort AS c
  CROSS JOIN params AS p
  JOIN events AS e
    ON e.user_id = c.user_id
   AND e.event_name = 'core_action_completed'
   AND e.event_at >= ((c.signup_day + 7)::timestamp AT TIME ZONE p.tz)
   AND e.event_at < ((c.signup_day + 8)::timestamp AT TIME ZONE p.tz)
)
SELECT
  c.signup_day AS cohort_day,
  c.acquisition_channel,
  COUNT(*) AS cohort_size,
  COUNT(r.user_id) AS retained_users,
  ROUND(
    COUNT(r.user_id)::numeric / NULLIF(COUNT(*), 0),
    4
  ) AS d7_retention_rate
FROM mature_cohort AS c
LEFT JOIN retained AS r
  ON r.user_id = c.user_id
GROUP BY c.signup_day, c.acquisition_channel
ORDER BY c.signup_day, c.acquisition_channel;

कोहोर्ट फ़िल्टर अनुक्रमित timestamptz मानों के साथ तुलना करने से पहले जनवरी की स्थानीय-मध्यरात्रि सीमाओं को UTC क्षणों में परिवर्तित करता है। यह WHERE क्लॉज में प्रत्येक signup_at पंक्ति पर दिनांक रूपांतरण लागू करने से बेहतर है: यह सामान्य रेंज स्कैन के लिए टाइमस्टैम्प कॉलम को योग्य रखते हुए स्थानीय-दिनांक नियम को बताता है।

तिथियों में मैच्योरिटी के बारे में तर्क करना आसान है। वॉटरमार्क स्थानीय तिथि 1 फरवरी में परिवर्तित हो जाता है। एक लक्षित तिथि 1 फरवरी से कड़ाई से पहले होनी चाहिए, जिसका अर्थ है कि इसकी समापन मध्यरात्रि कवर की गई है। 24 जनवरी प्लस सात 31 जनवरी है और पास हो जाता है। 25 जनवरी प्लस सात 1 फरवरी है और विफल हो जाता है। यदि वॉटरमार्क स्थानीय मध्यरात्रि के बजाय दोपहर का होता, तो मजबूत रूप सीधे वॉटरमार्क के साथ लक्षित दिन के अंतिम क्षण की तुलना करता:

((c.signup_day + 8)::timestamp AT TIME ZONE p.tz)
  <= p.data_complete_through

retained CTE एक सेमी-जॉइन जैसा यूज़र फ़्लैग बनाता है। DISTINCT प्रत्येक यूज़र को अधिकतम एक पंक्ति का योगदान करने देता है, भले ही इवेंट उत्पादक ने पुनः प्रयास किया हो या यूज़र ने मुख्य कार्रवाई दस बार पूरी की हो। चूँकि एक यूज़र ठीक एक कोहोर्ट पंक्ति से संबंधित है, इसलिए बताए गए स्कीमा के तहत user_id द्वारा जॉइन करना पर्याप्त है। यदि एक ही व्यक्ति के कई साइनअप एपिसोड हो सकते हैं, तो मॉडल को एक स्थिर एपिसोड पहचानकर्ता की आवश्यकता होगी और जॉइन इसका उपयोग करेगा।

अंतिम लेफ्ट जॉइन प्रत्येक मैच्योर कोहोर्ट सदस्य को सुरक्षित रखता है। इसलिए COUNT(*) डिनोमिनेटर को मापता है, जबकि COUNT(r.user_id) केवल मिलान किए गए रिटेन किए गए फ़्लैग्स की गणना करता है। NULLIF समूह स्तर पर रक्षात्मक है, हालांकि mature_cohort से उत्पन्न समूह में आवश्यक रूप से कम से कम एक पंक्ति होती है।

एक छोटे फ़िक्सचर के लिए, मान लें कि 1 जनवरी के ऑर्गेनिक कोहोर्ट में तीन यूज़र्स हैं और केवल एक के पास क्वालिफ़ाइंग Day-7 कार्रवाई है। इसका परिणाम 3, 1, और 0.3333 है। यदि 2 जनवरी के पेड कोहोर्ट में दो यूज़र्स हैं और दोनों वापस आते हैं, तो इसका परिणाम 2, 2, और 1.0000 है। 25 जनवरी का साइनअप बाद के इवेंट्स की परवाह किए बिना अनुपस्थित रहता है क्योंकि आपूर्ति किए गए वॉटरमार्क पर वह कोहोर्ट अपरिपक्व है।

कुल बनाने के लिए प्रदर्शित समूह दरों का औसत न निकालें। एक यूज़र के दैनिक कोहोर्ट का भार एक हज़ार यूज़र्स के कोहोर्ट के समान नहीं होना चाहिए। सही रोलअप है:

overall_retention = SUM(retained_users) / SUM(cohort_size)

प्रोडक्शन-आकार के डेटा पर, उपयोगी इंडेक्स चयनात्मक सीमाओं और जॉइन कुंजियों से मेल खाते हैं:

CREATE INDEX users_signup_at_idx
  ON users (signup_at);

CREATE INDEX events_core_action_user_time_idx
  ON events (user_id, event_at)
  WHERE event_name = 'core_action_completed';

पार्शियल इवेंट इंडेक्स केवल तभी उपयुक्त होता है जब यह इवेंट परिभाषा स्थिर और इसके राइट और स्टोरेज लागत को सही ठहराने के लिए पर्याप्त महत्वपूर्ण हो। एक बहुत बड़े इवेंट स्ट्रीम पर आवर्ती डैशबोर्ड के लिए, (user_id, activity_day, event_name) जैसी यूनिक कुंजी वाली एक वृद्धिशील रूप से अनुरक्षित तालिका बार-बार होने वाले रॉ-इवेंट स्कैन को हटा सकती है। इसका activity_day उसी नामित-ज़ोन अनुबंध के तहत प्राप्त किया जाना चाहिए; अन्यथा अनुकूलन मेट्रिक को बदल देता है।

मान लें कि U योग्य यूज़र्स हैं और E जॉइन के माध्यम से जांची गई प्रासंगिक इवेंट पंक्तियाँ हैं। कोहोर्ट स्कैन चयनित यूज़र्स में रैखिक है, डिडप्लीकेशन आमतौर पर रिटेन किए गए उम्मीदवारों पर एक हैश या सॉर्ट होता है, और अंतिम एग्रीगेशन मैच्योर यूज़र्स में रैखिक होता है। वास्तविक लागत इवेंट चयनात्मकता, इंडेक्स, सांख्यिकी और प्लान के आकार पर निर्भर करती है, इसलिए केवल SQL टेक्स्ट से सार्वभौमिक जटिलता का वादा करने के बजाय प्रतिनिधि डेटा पर EXPLAIN (ANALYZE, BUFFERS) का निरीक्षण करें।

उच्च-गुणवत्ता वाला नमूना उत्तर

"SQL लिखने से पहले, मैं चार परिभाषाओं को लॉक करूँगा: सटीक Day 7, रिटर्न इवेंट के रूप में core_action_completed, न्यूयॉर्क कैलेंडर तिथियां, और एक एक्सक्लूसिव पूर्णता वॉटरमार्क। डिनोमिनेटर प्रत्येक जनवरी का साइनअप है जिसकी पूरी Day-7 तिथि का अवलोकन किया गया है; न्यूमरेटर उस सेट से अलग यूज़र्स हैं जिनके पास कम से कम एक क्वालिफ़ाइंग इवेंट है।

मैं पहले कोहोर्ट का निर्माण करूँगा। मैं signup_at रेंज फ़िल्टर के लिए जनवरी की स्थानीय शुरुआत और अंत की मध्यरात्रियों को UTC क्षणों में बदलता हूँ, फिर प्रत्येक यूज़र की स्थानीय साइनअप तिथि और साइनअप-समय चैनल को बनाए रखता हूँ। मैं मैच्योरिटी को अलग से फ़िल्टर करता हूँ ताकि इसका ऑडिट किया जा सके: 1 फरवरी के स्थानीय-मध्यरात्रि वॉटरमार्क के साथ, 24 जनवरी अंतिम शामिल साइनअप तिथि है।

प्रत्येक मैच्योर यूज़र के लिए, मैं user_id, लक्षित इवेंट नाम, और signup_day + 7 पर स्थानीय मध्यरात्रि से signup_day + 8 पर स्थानीय मध्यरात्रि तक के हाफ़-ओपन अंतराल पर इवेंट्स को जॉइन करता हूँ। उन सीमाओं की गणना नामित समय क्षेत्र में की जाती है, इसलिए DST संक्रमण कैलेंडर नियम को 168-घंटे के नियम में नहीं बदलता है। मैं विशिष्ट यूज़र आईडी का चयन करता हूँ, कोहोर्ट में रिटेन किए गए फ़्लैग्स को लेफ्ट-जॉइन करता हूँ, और साइनअप तिथि और चैनल द्वारा एग्रीगेट करता हूँ। वह लेफ्ट जॉइन आवश्यक है क्योंकि बिना रिटर्न वाले यूज़र्स अभी भी डिनोमिनेटर से संबंधित हैं।

मैं स्वतंत्र रूप से CTEs को मान्य करूँगा। कोहोर्ट CTE में प्रति जनवरी साइनअप एक पंक्ति होनी चाहिए; मैच्योरिटी CTE को इस वॉटरमार्क के लिए 24 जनवरी को रुकना चाहिए; रिटेन्ड CTE में यूनिक यूज़र्स होने चाहिए; और अंतिम गणनाओं को 0 <= retained_users <= cohort_size को संतुष्ट करना चाहिए। फ़िक्सचर्स डुप्लिकेट इवेंट्स, कोई रिटर्न नहीं, गलत इवेंट नाम, Day 6 और Day 8, स्थानीय मध्यरात्रि के दोनों पक्षों, एक DST तिथि, नल चैनल्स, और एक लेट इवेंट बैकफ़िल को कवर करेंगे। समूहों में कुल के लिए, मैं दरों का औसत निकालने के बजाय कुल रिटेन किए गए यूज़र्स को कुल कोहोर्ट यूज़र्स से विभाजित करूँगा।"

सामान्य गलतियाँ

• एक इनर जॉइन का उपयोग करना। यह ज़ीरो-रिटर्न यूज़र्स को छोड़ देता है और दर को बढ़ा देता है। पहले कोहोर्ट का निर्माण करें और यूज़र-स्तरीय रिटेन्ड फ़्लैग को लेफ्ट-जॉइन करें।
• यूज़र्स के बजाय इवेंट्स की गिनती करना। COUNT(e.event_id) कोहोर्ट आकार से अधिक हो सकता है। न्यूमरेटर को यूज़र द्वारा डिडप्लिकेट करें या बूलियन अस्तित्व परीक्षण का उपयोग करें।
• Day 7 को अस्पष्ट छोड़ना। Days 1–7 या Day 7 के बाद को कवर करने वाला प्रेडिकेट एक अलग मेट्रिक की गणना करता है। SQL से पहले शब्दों में अंतराल परिभाषा लिखें।
• साइनअप टाइमस्टैम्प प्लस 168 घंटे से तुलना करना। वह बीता हुआ समय रिटेंशन है, नामित-ज़ोन कैलेंडर-तिथि परिभाषा नहीं, और यह डेलाइट-सेविंग परिवर्तनों के आसपास भिन्न हो सकता है।
• फ़िल्टर के अंदर अनुक्रमित टाइमस्टैम्प को कास्ट करना। प्रत्येक signup_at को एक तारीख में बदलना एक उपयोगी रेंज स्कैन को रोक सकता है। इसके बजाय स्थिर स्थानीय सीमाओं को क्षणों में बदलें।
• अपरिपक्व कोहोर्ट्स को शामिल करना। हाल के यूज़र्स को वापस लौटने का पूरा अवसर नहीं मिला है, जिससे कृत्रिम रूप से कम प्रदर्शन पैदा होता है। केवल वर्तमान समय पर नहीं, पाइपलाइन वॉटरमार्क पर गेट करें।
• वॉटरमार्क को स्थायी सत्य मानना। बैकफ़िल पहले से रिपोर्ट किए गए कोहोर्ट्स को बदल सकते हैं। पुनर्गणना और ताज़गी व्यवहार को परिभाषित करें।
• एक परिवर्तनशील चैनल मान पढ़ना। वर्तमान एट्रिब्यूशन ऐतिहासिक कोहोर्ट्स में भविष्य की जानकारी लीक कर सकता है। एक अपरिवर्तनीय साइनअप फ़ील्ड या वर्ज़नयुक्त स्नैपशॉट का उपयोग करें।
• समूह प्रतिशतों का औसत निकालना। कोहोर्ट आकार भिन्न होने पर भारहीन औसत कुल को विकृत कर देते हैं। पहले काउंट्स को रोल अप करें।
• खाली और नल सेमेंटिक्स को अनदेखा करना। नल चैनल्स को जानबूझकर मैप करें, और मेट्रिक प्रकाशित करने से पहले यह तय करें कि क्या लापता इवेंट डेटा का अर्थ शून्य गतिविधि है या एक अधूरी पाइपलाइन।

फॉलो-अप प्रश्न और उत्तर

आप Days 1–7 के भीतर रिटेंशन की गणना कैसे करेंगे?

समान कोहोर्ट और मैच्योरिटी दृष्टिकोण रखें, लेकिन इवेंट अंतराल को signup_day + 1 पर स्थानीय मध्यरात्रि से शुरू करने और signup_day + 8 पर स्थानीय मध्यरात्रि से पहले समाप्त करने के लिए बदलें। मैच्योरिटी के लिए अभी भी पूरे सातवें दिन का पूरा होना आवश्यक है। बताएं कि क्या Day 0 को गिना जाना चाहिए; इस पर प्रोडक्ट टूल्स और टीमें भिन्न हैं।

आप "Day 7 या बाद में" रिटेंशन की गणना कैसे करेंगे?

निचली सीमा signup_day + 7 पर स्थानीय मध्यरात्रि बनी रहती है, लेकिन ऊपरी सीमा रिपोर्टिंग कटऑफ़ बन जाती है। वह मेट्रिक संचयी है और अवलोकन की लंबाई पर निर्भर करता है: एक पुराने कोहोर्ट के पास वापस आने के अधिक मौके होते हैं। केवल एक सामान्य आयु में कोहोर्ट्स की तुलना करें या एक अनकैप्ड संख्या के बजाय एक रिटेंशन वक्र प्रकाशित करें।

क्या PostgreSQL एक अलग retained CTE के बिना इसे एग्रीगेट कर सकता है?

हाँ। एक लेटरल EXISTS लुकअप या सावधानीपूर्वक निर्मित एग्रीगेट प्रति कोहोर्ट यूज़र एक बूलियन लौटा सकता है। एक सीधा जॉइन प्लस COUNT(DISTINCT e.user_id) FILTER (WHERE ...) भी संभव है, लेकिन यह एग्रीगेशन से पहले कई इवेंट पंक्तियों को भौतिक (materialize) कर सकता है। अलग CTE ग्रेन और शुद्धता के प्रमाण का ऑडिट करना आसान बनाता है; मापे गए डेटा से अंतिम प्लान चुनें।

क्या होगा यदि प्रत्येक यूज़र का रिपोर्टिंग टाइम ज़ोन अलग हो?

उस ज़ोन को संग्रहीत करें जो साइनअप एपिसोड पर लागू होता है और उसी मान से कोहोर्ट दिन और लक्षित सीमा दोनों प्राप्त करें। ऐतिहासिक ज़ोन परिवर्तनों के लिए एक घोषित नीति की आवश्यकता होती है। प्रति-यूज़र ज़ोन का अर्थ यह भी है कि एक कैलेंडर कोहोर्ट लेबल विभिन्न UTC अंतरालों में फैला हुआ है, इसलिए प्री-एग्रीगेशन में लागू ज़ोन या पहले से सामान्यीकृत स्थानीय दिन को बनाए रखना चाहिए।

आप मैच्योरिटी सीमा का परीक्षण कैसे करेंगे?

आपूर्ति किए गए वॉटरमार्क के तहत 24 जनवरी और 25 जनवरी को साइनअप बनाएं। दोनों को एक मान्य लक्षित-दिन का इवेंट दें। 24 जनवरी दिखाई देना चाहिए और 25 जनवरी नहीं दिखाई देना चाहिए। एक्सक्लूसिव-बाउंड्री नियम की पुष्टि करने के लिए लक्षित दिन की समापन मध्यरात्रि से ठीक एक सेकंड पहले और सटीक मध्यरात्रि पर वॉटरमार्क का परीक्षण भी करें।

देर से आने वाले इवेंट्स को कैसे संभाला जाना चाहिए?

इवेंट-टाइम वॉटरमार्क प्रकाशित करें और उन सभी कोहोर्ट्स की पुनर्गणना करें जिनके योग्य इवेंट अंतराल बैकफ़िल के साथ ओवरलैप होते हैं। यदि इंजेशन में देरी का एक ज्ञात सेवा स्तर है, तो रिपोर्ट नाममात्र Day-7 क्लोज़ से आगे एक सुरक्षा देरी (safety delay) जोड़ सकती हैं। रॉ काउंट और मेट्रिक वर्ज़न बनाए रखें ताकि सुधार का पता लगाया जा सके।

आप प्रोडक्शन में किन इनवेरिएंट्स की निगरानी करेंगे?

जांचें कि प्रत्येक समूह में सकारात्मक cohort_size है, रिटेन किए गए यूज़र्स शून्य और कोहोर्ट आकार के बीच रहते हैं, कोई अपरिपक्व कोहोर्ट तिथि मौजूद नहीं है, कोहोर्ट का कुल योग साइनअप स्रोत के साथ मेल खाता है, और अधिकतम इवेंट समय घोषित वॉटरमार्क को कवर करता है। इवेंट-वॉल्यूम या इंजेशन-विलंब विसंगतियों पर अलग से अलर्ट करें ताकि पाइपलाइन गैप को रिटेंशन में गिरावट के रूप में गलत न समझा जाए।