SQL इंटरव्यू: प्रत्येक यूज़र की सबसे लंबी लगातार लॉगिन स्ट्रीक ज्ञात करना

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

आपको एक PostgreSQL इवेंट टेबल दी गई है:

CREATE TABLE user_logins (
  user_id bigint NOT NULL,
  login_at timestamptz NOT NULL
);

प्रत्येक यूज़र के लिए, लगातार America/New_York कैलेंडर तारीखों की प्रत्येक सबसे लंबी अवधि लौटाएं, जिस पर यूज़र ने कम से कम एक बार लॉगिन किया हो। आउटपुट कॉलम user_id, streak_start, streak_end और streak_days हैं। एक ही स्थानीय तारीख पर कई इवेंट्स को एक सक्रिय दिन माना जाता है। कोई भी छूटी हुई स्थानीय तारीख स्ट्रीक को तोड़ देती है। यदि किसी यूज़र के पास समान लंबाई की दो सबसे लंबी स्ट्रीक हैं, तो दोनों को लौटाएं। बिना किसी इवेंट वाले यूज़र परिणाम में नहीं आते हैं।

यह डेटा और SQL इंटरव्यू की समस्या है, बीते हुए 24-घंटे की अवधियों को गिनने का अनुरोध नहीं। डेलाइट-सेविंग ट्रांज़िशन के आसपास एक स्थानीय दिन में 23 या 25 घंटे हो सकते हैं और फिर भी वह एक कैलेंडर तारीख ही होता है। इसलिए प्रश्न टाइमस्टैम्प को तारीखों में बदलने से पहले रिपोर्टिंग टाइम ज़ोन को तय करता है। मुख्य उत्तर PostgreSQL को लक्षित करता है; अन्य बोलियों (dialects) को अलग दिनांक अंकगणित (date arithmetic) की आवश्यकता होती है।

मुख्य कार्य गैप्स-एंड-आइलैंड्स (gaps-and-islands) की समस्या है: क्रमित तारीखों को इस प्रकार रूपांतरित करें कि एक लगातार अवधि की प्रत्येक तारीख एक स्थिर की (key) साझा करे, प्रत्येक की (key) को एक आइलैंड में एग्रीगेट करें, और फिर प्रति यूज़र अधिकतम लंबाई के साथ बराबरी करने वाले सभी आइलैंड्स को बनाए रखें।

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

पहला संकेत यह है कि क्या उम्मीदवार विंडो फ़ंक्शन लिखने से पहले ग्रेन (grain) को परिभाषित करता है। स्रोत का ग्रेन एक लॉगिन इवेंट है, लेकिन बिज़नेस का ग्रेन प्रति यूज़र और स्थानीय कैलेंडर तारीख एक पंक्ति है। उस रूपांतरण को छोड़ने से एक ही दिन के डुप्लिकेट इवेंट्स ROW_NUMBER(), काउंट्स और स्ट्रीक की सीमाओं को बढ़ा देते हैं।

दूसरा संकेत यह है कि क्या उम्मीदवार आइलैंड की (island key) प्राप्त कर सकता है। विशिष्ट तारीखों को सॉर्ट करने के बाद, एक लगातार रन के भीतर तारीख और ROW_NUMBER() दोनों एक से आगे बढ़ते हैं। इसलिए प्रत्येक तारीख से रो-नंबर ऑफ़सेट घटाने पर उस पूरे रन के दौरान समान मान प्राप्त होता है। एक गैप पर, रो नंबर ठीक एक से आगे बढ़ता है जबकि तारीख एक से अधिक दिनों से कूदती है, इसलिए की (key) बदल जाती है।

तीसरा संकेत अनुबंध अनुशासन (contract discipline) है। जब दो रनों की लंबाई समान होती है तो "सबसे लंबी स्ट्रीक" अस्पष्ट होती है। एक क्वेरी जो एक परिणाम चुनने के लिए ROW_NUMBER() का उपयोग करती है, वह वैध टाई को चुपचाप हटा देती है। इस प्रॉम्प्ट में हर टाई वाले अधिकतम की आवश्यकता है, इसलिए उत्तर प्रत्येक आइलैंड की लंबाई की तुलना उस यूज़र के लिए अधिकतम आइलैंड लंबाई से करता है।

चौथा संकेत टाइम-ज़ोन की शुद्धता है। login_at को सीधे date में कास्ट करने से डेटाबेस सेशन टाइम ज़ोन का उपयोग होता है, जो अलग-अलग वातावरणों के बीच भिन्न हो सकता है। उत्तर पहले प्रत्येक timestamptz को एक नामित बिज़नेस ज़ोन में बदलता है और उसके बाद ही तारीख लेता है। एक निश्चित UTC ऑफ़सेट ऐसे ज़ोन के लिए अपर्याप्त है जिसका ऑफ़सेट डेलाइट-सेविंग नियमों के साथ बदलता है।

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

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

• एक दिन को क्या परिभाषित करता है? एक नामित बिज़नेस टाइम ज़ोन, UTC, या प्रत्येक यूज़र का अपना ज़ोन तारीख के

रूपांतरण और संभवतः उत्तर को बदल देता है। यह प्रॉम्प्ट प्रत्येक यूज़र के लिए America/New_York का उपयोग करता है।

• क्या एक दिन में कई लॉगिन एक से अधिक बार गिने जाते हैं? वे यहाँ एक से अधिक बार नहीं गिने जाते, इसलिए नंबरिंग से पहले

डिडुप्लिकेशन (deduplication) होना चाहिए। यदि मीट्रिक इसके बजाय लगातार इवेंट्स होता, तो ग्रेन और ग्रुपिंग का नियम बदल जाता।

• क्या एक छूटी हुई तारीख हमेशा स्ट्रीक को तोड़ती है? हाँ। 30-मिनट की सीमा वाले सेशनलाइज़ेशन प्रश्न में

सख्त कैलेंडर निकटता के बजाय पिछली-पंक्ति की तुलना की आवश्यकता होती है।

• टाई कैसे लौटाई जानी चाहिए? यह अनुबंध प्रत्येक टाई वाले सबसे लंबे आइलैंड को लौटाता है। सबसे हाल की

स्ट्रीक चुनने के लिए एक अलग, स्पष्ट टाई-ब्रेकर की आवश्यकता होगी।

• क्या सीमा बंधी हुई (bounded) है? एक दिनांक फ़िल्टर काम को कम कर सकता है, लेकिन यह उन स्ट्रीक्स को भी छोटा कर देता है जो

रेंज से पहले शुरू होती हैं। कॉलर को यह बताना चाहिए कि क्या परिणाम "रेंज के भीतर" है या इसकी सीमा को पार करने वाली पूरी स्ट्रीक है।

• क्या user_id या login_at नल (null) हो सकते हैं? स्कीमा कहता है कि नहीं। यदि नल की अनुमति होती, तो

ऑर्डरिंग या ग्रुपिंग से पहले उनके उपचार को निर्दिष्ट करने की आवश्यकता होती।

• क्या यह वन-ऑफ़ क्वेरी है या आवर्ती उत्पाद मीट्रिक? एक तदर्थ (ad hoc) उत्तर दैनिक पंक्तियों को स्कैन और सॉर्ट कर

सकता है। बार-बार रीफ़्रेश होने वाला डैशबोर्ड वृद्धिशील रूप से बनाए रखी जाने वाली (incrementally maintained) यूज़र-डे टेबल को उचित ठहरा सकता है।

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

"मैं पहले प्रत्येक timestamptz को सहमत बिज़नेस टाइम ज़ोन में बदलूँगा और प्रति यूज़र और स्थानीय तारीख एक पंक्ति में डिडुप्लिकेट करूँगा। प्रत्येक यूज़र के भीतर, मैं उन तारीखों को क्रमबद्ध करता हूँ और ROW_NUMBER() असाइन करता हूँ। सख्त लगातार तारीखों के लिए, login_day - row_number × one day एक स्ट्रीक के भीतर स्थिर रहता है और एक गैप के बाद बदल जाता है, इसलिए मैं प्रत्येक स्ट्रीक की सीमाओं और लंबाई को प्राप्त करने के लिए उस व्युत्पन्न की (derived key) द्वारा समूह बनाता हूँ। फिर मैं प्रत्येक लंबाई की तुलना यूज़र के अधिकतम से करता हूँ, जो टाई को बनाए रखता है। मैं एक ही दिन के डुप्लिकेट इवेंट्स, एक-दिन की स्ट्रीक्स, गैप्स, समान अधिकतम, स्थानीय-मध्यरात्रि और डेलाइट-सेविंग मामलों का परीक्षण करूँगा, और प्रतिनिधि डेटा पर प्लान का निरीक्षण करूँगा।"

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

इवेंट स्ट्रीम को बिज़नेस ग्रेन में सामान्यीकृत करके प्रारंभ करें। timestamptz के लिए, एक नामित ज़ोन के साथ AT TIME ZONE उस ज़ोन में वॉल-क्लॉक टाइमस्टैम्प उत्पन्न करता है। उस परिणाम को date में कास्ट करने से बिज़नेस कैलेंडर तारीख मिलती है। इसके बाद SELECT DISTINCT प्रति यूज़र-दिन ठीक एक पंक्ति की गारंटी देता है।

पूरी क्वेरी है:

WITH login_days AS (
  SELECT DISTINCT
    user_id,
    (login_at AT TIME ZONE 'America/New_York')::date AS login_day
  FROM user_logins
),
numbered AS (
  SELECT
    user_id,
    login_day,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY login_day
    ) AS rn
  FROM login_days
),
grouped AS (
  SELECT
    user_id,
    login_day,
    login_day - (rn * INTERVAL '1 day') AS island_key
  FROM numbered
),
streaks AS (
  SELECT
    user_id,
    MIN(login_day) AS streak_start,
    MAX(login_day) AS streak_end,
    COUNT(*) AS streak_days
  FROM grouped
  GROUP BY user_id, island_key
),
scored AS (
  SELECT
    user_id,
    streak_start,
    streak_end,
    streak_days,
    MAX(streak_days) OVER (PARTITION BY user_id) AS max_streak_days
  FROM streaks
)
SELECT
  user_id,
  streak_start,
  streak_end,
  streak_days
FROM scored
WHERE streak_days = max_streak_days
ORDER BY user_id, streak_start;

प्रमाण क्रमबद्ध दैनिक पंक्तियों का अनुसरण करता है। एक यूज़र के लिए, विशिष्ट तारीखों को d1, d2, ... और रो नंबरों को 1, 2, ... कहें। यदि d(i+1) = d(i) + 1 day है, तो अगले रो-नंबर ऑफ़सेट को घटाने पर वही अतिरिक्त दिन हट जाता है, इसलिए व्युत्पन्न कुंजियाँ समान होती हैं। यदि कम से कम एक तारीख गायब है, तो d(i+1) दो या अधिक दिनों से आगे बढ़ता है जबकि रो नंबर एक से आगे बढ़ता है; व्युत्पन्न की (derived key) बढ़ जाती है और एक नया ग्रुप शुरू करती है। डिडुप्लिकेशन COUNT(*) को कैलेंडर दिनों के बराबर बनाता है, जबकि MIN और MAX सटीक आइलैंड सीमाएँ हैं।

अंतिम अधिकतम जानबूझकर एक विंडोयुक्त MAX है, कोई अन्य ROW_NUMBER() नहीं। प्रत्येक आइलैंड जिसकी लंबाई यूज़र के अधिकतम के बराबर है, बच जाता है। यदि उत्पाद बाद में केवल एक स्ट्रीक मांगता है, तो एक घोषित नियम जोड़ें जैसे "नवीनतम समाप्ति तिथि जीतती है" और नियतात्मक ऑर्डरिंग का उपयोग करें; क्वेरी के अंदर उस नियम का आविष्कार न करें।

दो यूज़र्स के लिए सामान्यीकृत तारीखों पर विचार करें:

user 1: Mar 07, Mar 08, Mar 09, Mar 11, Mar 12
user 2: Nov 01, Nov 02, Nov 04, Nov 05

result:
user 1 | Mar 07 | Mar 09 | 3
user 2 | Nov 01 | Nov 02 | 2
user 2 | Nov 04 | Nov 05 | 2

यूज़र 1 का अधिकतम तीन दिन है। यूज़र 2 के पास दो अलग-अलग दो-दिन के अधिकतम हैं, इसलिए दोनों पंक्तियों की आवश्यकता है। प्रदर्शित किसी भी तारीख पर कई कच्चे इवेंट्स परिणाम को नहीं बदलते हैं। डेलाइट-सेविंग परिवर्तन के आसपास, प्रासंगिक प्रश्न यह बना रहता है कि क्या स्थानीय तारीखें आसन्न (adjacent) हैं, न कि क्या टाइमस्टैम्प ठीक 24 घंटे की दूरी पर हैं।

N कच्चे इवेंट्स और D विशिष्ट यूज़र-डे पंक्तियों के लिए, डिडुप्लिकेशन N पंक्तियों को पढ़ता है और हैश या सॉर्ट कर सकता है; विंडो चरण यूज़र और तारीख के अनुसार D पंक्तियों तक को सॉर्ट करता है। इंटरव्यू के लिए एक उपयोगी सीमा सॉर्ट-आधारित योजना में O(N log N + D log D) समय और O(D) मध्यवर्ती स्थान है, साथ ही यह ध्यान में रखते हुए कि ऑप्टिमाइज़र हैश, मौजूदा क्रम, समानांतरता (parallelism), या डिस्क स्पिल का उपयोग कर सकता है। निष्पादन योजना (execution plan), न कि केवल Big-O, यह तय करती है कि प्रोडक्शन क्वेरी स्वीकार्य है या नहीं।

अरबों इवेंट्स पर एक आवर्ती मीट्रिक के लिए, (user_id, login_day) पर एक अद्वितीय कुंजी के साथ एक वृद्धिशील रूप से अनुरक्षित (incrementally maintained) तालिका बनाएं। यह टाइम-ज़ोन रूपांतरण और उसी दिन के डिडुप्लिकेशन को इनजेशन या बैच सीमा पर स्थानांतरित कर देता है, ताकि स्ट्रीक क्वेरी N इवेंट्स के बजाय D दैनिक पंक्तियों को पढ़े। यदि किसी तदर्थ क्वेरी की समय सीमा है, तो स्थानीय-तारीख रूपांतरण से पहले sargable UTC टाइमस्टैम्प सीमाएं लागू करें, लेकिन उन UTC सीमाओं को नामित-ज़ोन की स्थानीय मध्यरात्रि से प्राप्त करें ताकि डेलाइट-सेविंग परिवर्तनों का सम्मान किया जा सके।

अंतिम तालिका को प्रमाण मानने के बजाय मध्यवर्ती परिणामों का निरीक्षण करें:

-- These checks are run against the corresponding CTE or materialized test result.
SELECT user_id, login_day, COUNT(*)
FROM login_days
GROUP BY user_id, login_day
HAVING COUNT(*) > 1;

SELECT *
FROM numbered
ORDER BY user_id, login_day;

SELECT *
FROM streaks
WHERE streak_days <> (streak_end - streak_start + 1);

पहले और तीसरे चेक में कोई पंक्ति नहीं लौटनी चाहिए। क्रमांकित आउटपुट गलत ग्रेन या ऑर्डरिंग को दृश्यमान बनाता है। स्कैन, सॉर्ट, रो अनुमान, अस्थायी I/O, और क्या कच्चे इवेंट्स को पहले कम करने से फर्क पड़ेगा, यह देखने के लिए EXPLAIN (ANALYZE, BUFFERS) उपसर्ग के साथ पूरी क्वेरी चलाएं। जब प्रोडक्शन स्टेटमेंट को स्वयं निष्पादित करना महंगा हो, तो एक सुरक्षित प्रतिनिधि प्रतिलिपि का उपयोग करें।

शिफ़्ट की गई तारीख (shifted-date) की तकनीक सार्वभौमिक नहीं है। यदि अंतराल 30 मिनट से अधिक होने पर एक नया सेशन शुरू होता है, या स्थिति मान अपरिवर्तित रहने तक एक आइलैंड जारी रहता है, तो पिछली पंक्ति का निरीक्षण करने के लिए LAG() का उपयोग करें, प्रत्येक सीमा को फ़्लैग करें, और उन फ़्लैग्स का रनिंग SUM() लें। निर्णय का नियम सरल है: सख्त इकाई-दर-इकाई अनुक्रमों के लिए शिफ़्ट की गई कुंजी का उपयोग करें; जब निरंतरता कस्टम तुलना पर निर्भर करती है तो सीमा फ़्लैग्स का उपयोग करें।

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

"SQL लिखने से पहले, मैं ग्रेन और टाई अनुबंध को लॉक करूँगा। स्रोत में प्रति यूज़र कई इवेंट्स हैं, लेकिन मीट्रिक प्रति यूज़र एक America/New_York कैलेंडर तारीख की गणना करता है। इसलिए मैं किसी भी विंडो फ़ंक्शन से पहले timestamptz को उस नामित ज़ोन में बदलूँगा, date में कास्ट करूँगा, और डिडुप्लिकेट करूँगा। यह सेशन टाइम ज़ोन को परिणाम को चुपचाप बदलने से भी रोकता है।

गैप्स-एंड-आइलैंड्स चरण के लिए, मैं प्रत्येक यूज़र के भीतर स्थानीय तिथि के अनुसार क्रमबद्ध ROW_NUMBER() असाइन करता हूँ। एक लगातार रन के दौरान, तारीख और रो नंबर दोनों एक से आगे बढ़ते हैं, इसलिए रो-नंबर डे ऑफ़सेट को घटाने पर एक स्थिर की (key) प्राप्त होती है। एक छूटी हुई तारीख के कारण तारीख रो नंबर की तुलना में अधिक आगे कूदती है और की (key) बदल जाती है। यूज़र और उस की (key) पर ग्रुपिंग करने से प्रत्येक स्ट्रीक के लिए प्रारंभ, अंत और सक्रिय तारीखों की संख्या मिलती है।

मैं स्ट्रीक की लंबाई पर एक विंडोयुक्त अधिकतम का उपयोग करूँगा और उस अधिकतम के साथ समानता बनाए रखूँगा। यह आवश्यकतानुसार सभी टाई वाली सबसे लंबी स्ट्रीक्स लौटाता है, न कि चुपचाप किसी एक को चुनता है। सॉर्ट-आधारित ऊपरी सीमा लगभग O(N log N + D log D) है, जहाँ N कच्चे इवेंट्स हैं और D विशिष्ट यूज़र-दिन हैं, हालांकि मैं वास्तविक प्लान और स्पिल्स का निरीक्षण करूँगा।

मेरे परीक्षण डेटा में एक ही तारीख पर कई इवेंट्स, एक दिन का यूज़र, एक छूटी हुई तारीख, दो समान अधिकतम, स्थानीय मध्यरात्रि के दोनों तरफ के इवेंट्स और एक डेलाइट-सेविंग ट्रांज़िशन शामिल होंगे। बड़े पैमाने के आवर्ती मीट्रिक के लिए, मैं एक अद्वितीय यूज़र-डे टेबल बनाए रखूँगा और उस छोटे ग्रेन पर विंडो लॉजिक चलाऊँगा। यदि निरंतरता कैलेंडर निकटता से थ्रेशोल्ड गैप में बदल जाती है, तो मैं LAG() के साथ सीमा फ़्लैग्स और एक रनिंग सम पर स्विच करूँगा।"

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

• कच्चे लॉगिन इवेंट्स को नंबर देना → डुप्लिकेट इवेंट्स रो नंबर को आगे बढ़ाते हैं और काउंट्स को बढ़ा देते हैं →

विंडो लागू करने से पहले एक यूज़र-डे पंक्ति में डिडुप्लिकेट करें।

• timestamptz को सीधे date में कास्ट करना → उत्तर सेशन टाइम ज़ोन पर निर्भर करता है →

पहले नामित बिज़नेस ज़ोन में कनवर्ट करें।

• एक निश्चित UTC ऑफ़सेट का उपयोग करना → जब नामित ज़ोन ऑफ़सेट बदलता है तो स्थानीय तारीखें गलत हो जाती हैं →

इसके कैलेंडर नियमों के साथ एक IANA ज़ोन का उपयोग करें।

• 24 घंटे अलग टाइमस्टैम्प की तुलना करना → 23-घंटे या 25-घंटे के स्थानीय दिन मान्य कैलेंडर स्ट्रीक्स को तोड़ देते हैं

→ स्थानीय तारीखों की तुलना करें, क्योंकि अनुबंध कैलेंडर निकटता का है।

• केवल शिफ़्ट की गई तारीख द्वारा ग्रुपिंग करना → समान व्युत्पन्न कुंजी वाले यूज़र्स एक साथ मर्ज हो जाते हैं → **user_id

और island_key दोनों द्वारा ग्रुप करें।**

• ROW_NUMBER() के साथ एक पंक्ति लेना → टाई वाली सबसे लंबी स्ट्रीक्स खारिज हो जाती हैं → **प्रत्येक आइलैंड की

तुलना प्रति-यूज़र अधिकतम से करें।**

• सीमा नियम के बिना रिपोर्टिंग अंतराल को फ़िल्टर करना → प्रारंभ तिथि को पार करने वाली स्ट्रीक

छोटी हो जाती है और इसे गलत लेबल किया जा सकता है → परिभाषित करें कि क्या परिणाम रेंज-लोकल हैं या पूर्ण आइलैंड्स।

• पहली पंक्ति को संभाले बिना LAG() का उपयोग करना → पहले आइलैंड में सीमा का अभाव होता है → **एक नल (null)

पिछली पंक्ति को एक समूह की शुरुआत मानें।**

• केवल Big-O उद्धृत करना → सॉर्ट स्पिल या खराब कार्डिनैलिटी अनुमान अदृश्य रहता है → **मध्यवर्ती

काउंट्स और EXPLAIN (ANALYZE, BUFFERS) का निरीक्षण करें।**

• प्रत्येक डैशबोर्ड रीफ़्रेश के लिए कच्चे इतिहास को स्कैन करना → बार-बार रूपांतरण और डिडुप्लिकेशन लागत पर

हावी होते हैं → जब वर्कलोड इसे उचित ठहराता है तो एक अद्वितीय यूज़र-डे ग्रेन बनाए रखें।

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

फॉलो-अप 1: आप केवल सबसे हाल की सबसे लंबी स्ट्रीक कैसे लौटाएंगे?

समान आइलैंड निर्माण रखें। स्ट्रीक्स की गणना करने के बाद, उन्हें प्रति यूज़र streak_days DESC, फिर streak_end DESC, और अंत में streak_start DESC द्वारा नियतात्मक अंतिम टाई-ब्रेकर के रूप में रैंक करें। रैंक एक लौटाएं। बताएं कि यह आउटपुट अनुबंध को बदलता है: समान लंबाई वाली सभी स्ट्रीक्स अब नहीं बचती हैं।

फॉलो-अप 2: यदि कोई सेशन 30 मिनट की निष्क्रियता के बाद समाप्त होता है तो क्या बदलता है?

कैलेंडर घटाव अब निरंतरता का मॉडल नहीं बनता है। टाइमस्टैम्प द्वारा इवेंट्स को क्रमबद्ध करें, प्रति यूज़र LAG(login_at) का उपयोग करें, पहली पंक्ति या 30 मिनट से अधिक के किसी भी अंतराल को एक नए सेशन के रूप में चिह्नित करें, और एक स्पष्ट ROWS UNBOUNDED PRECEDING फ्रेम के साथ उस फ़्लैग का एक रनिंग SUM परिकलित करें। यूज़र और जनरेट की गई सेशन आईडी द्वारा एग्रीगेट करें।

फॉलो-अप 3: आप प्रत्येक यूज़र के अपने टाइम ज़ोन को कैसे संभालते हैं?

इवेंट को एक संस्करणित (versioned) यूज़र-टाइम-ज़ोन मान से जोड़ें जो इवेंट के समय के लिए मान्य हो, फिर तारीख लेने से पहले कनवर्ट करें। एक एकल वर्तमान प्रोफ़ाइल सेटिंग किसी यूज़र के स्थानांतरित होने के बाद ऐतिहासिक दिनों को फिर से लिख सकती है। स्पष्ट करें कि क्या उत्पाद तत्कालीन ज़ोन के तहत ऐतिहासिक गतिविधि को फ़्रीज रखना चाहता है या यूज़र के वर्तमान ज़ोन के तहत पुनर्गणना करना चाहता है; वे अलग-अलग मीट्रिक्स हैं।

फॉलो-अप 4: आप केवल पिछले 90 स्थानीय दिनों को कैसे क्वेरी करेंगे?

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

फॉलो-अप 5: आप इसे दैनिक डैशबोर्ड के लिए कुशल कैसे बनाएंगे?

एक अद्वितीय कुंजी और idempotent अप्सर्ट्स के साथ user_login_days(user_id, login_day) बनाए रखें। सहमत टाइम-ज़ोन नियम का उपयोग करके इसे इवेंट पाइपलाइन से अपडेट करें। केवल उन यूज़र्स की पुनर्गणना करें जिनकी दैनिक पंक्तियाँ बदल गई हैं, या देर से आने वाले इवेंट्स को समाहित करने के लिए समय-समय पर ओवरलैप विंडो से पुनर्निर्माण करें। प्रकाशित करने से पहले कच्चे स्रोत के विरुद्ध दैनिक-पंक्ति काउंट्स का मिलान करें।

फॉलो-अप 6: शिपिंग से पहले आपको किन परीक्षणों की आवश्यकता होगी?

डुप्लिकेट, एक-पंक्ति वाले यूज़र्स, आंतरिक अंतराल, टाई अधिकतम, स्थानीय-मध्यरात्रि इवेंट्स, डेलाइट-सेविंग प्रारंभ और अंत, देर से आने वाले इवेंट्स और रिपोर्टिंग-सीमा क्रॉसिंग के लिए टेबल-संचालित फिक्स्चर का उपयोग करें। दावा करें (Assert) कि दैनिक पंक्तियाँ अद्वितीय हैं, प्रत्येक आइलैंड streak_days = streak_end - streak_start + 1 को संतुष्ट करता है, और प्रत्येक लौटाई गई स्ट्रीक अपने यूज़र के अधिकतम के बराबर है। नमूना यूज़र्स पर कच्चे-इवेंट पुनर्गणना के साथ वृद्धिशील दैनिक तालिका की तुलना करें, फिर प्रोडक्शन जैसी कार्डिनैलिटी पर क्वेरी प्लान और अस्थायी I/O का निरीक्षण करें।