<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Zeynep Yılmaz on Medium]]></title>
        <description><![CDATA[Stories by Zeynep Yılmaz on Medium]]></description>
        <link>https://medium.com/@zeyneepyilmaz12?source=rss-d444764fee49------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*tMI8uYfaIcrm52Ea9wI2zA.jpeg</url>
            <title>Stories by Zeynep Yılmaz on Medium</title>
            <link>https://medium.com/@zeyneepyilmaz12?source=rss-d444764fee49------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 13:49:56 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@zeyneepyilmaz12/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Clickstream Data Validation: 5 Ways to Make It Reliable]]></title>
            <link>https://medium.com/@zeyneepyilmaz12/clickstream-data-validation-5-ways-to-make-it-reliable-f96019e7d83c?source=rss-d444764fee49------2</link>
            <guid isPermaLink="false">https://medium.com/p/f96019e7d83c</guid>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-validation]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[bigquery]]></category>
            <dc:creator><![CDATA[Zeynep Yılmaz]]></dc:creator>
            <pubDate>Fri, 18 Sep 2026 06:39:16 GMT</pubDate>
            <atom:updated>2026-09-18T06:39:16.144Z</atom:updated>
            <content:encoded><![CDATA[<p><em>Based on real clickstream data scenarios from e-commerce and travel platforms</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*jVQRhFp8oZaL5sn71PuB5A.png" /></figure><h3>Introduction</h3><p>In the previous article, I explained how window functions let me get more accurate results while scanning less data. But here’s the thing: no matter how well a query is written, the result is only as trustworthy as the source event data. And this kind of data corruption usually creeps in silently — the number on the dashboard looks “reasonable” so nobody questions it, until someone in a meeting asks “why did this number drop so much?”</p><p>In this article, I walk through 5 checks I run before trusting a dashboard or a metric, with real scenarios and queries. The examples are built around a typical raw_clickstream_events table that holds raw event logs from mobile and web clients.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uvKqD1r7U_ffENfmIergIw.png" /></figure><h3>Check 1: Duplicate Events</h3><h4><strong>Why It Matters</strong></h4><p>When a mobile app loses network connectivity, the client doesn’t get a confirmation from the server and resends the same event. A “purchase” event with the same event_id can end up appearing 2-3 times in the table. If these duplicates aren&#39;t cleaned, conversion rate and revenue metrics on the dashboard show up inflated — and this inflation is usually small enough to go unnoticed while still being systematic.</p><h4><strong>Sample Data</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-z_pbM7wC2zs3s9DxSNZSw.png" /></figure><h4><strong>Detection and Cleanup Query</strong></h4><pre>SELECT<br>  event_id,<br>  user_id,<br>  event_name,<br>  event_timestamp,<br>  ingestion_timestamp<br>FROM raw_clickstream_events<br>QUALIFY ROW_NUMBER() OVER (<br>  PARTITION BY event_id<br>  ORDER BY ingestion_timestamp ASC<br>) = 1</pre><p>QUALIFY filters based on a window function&#39;s result without needing an extra CTE. For each event_id, the row that arrived earliest (lowest ingestion_timestamp) is kept; which row counts as &quot;original&quot; becomes a clear, repeatable rule.</p><p><strong>Note — what if </strong><strong>ingestion_timestamp is also identical?</strong> In some cases, two rows with the same event_id can have an ingestion_timestamp that matches down to the millisecond (e.g. the client packages the same event twice within a single batch). In that case, ORDER BY ingestion_timestamp ASC alone doesn&#39;t guarantee which row gets picked — without a tiebreaker to break the ordering tie, BigQuery&#39;s result isn&#39;t deterministic. To prevent this, you need a second sort key (a tiebreaker) — an extra rule that decides which row comes first when there&#39;s a tie. A practical approach here is to combine all the row&#39;s columns into a summary value (a hash) and sort by that:</p><pre>SELECT<br>  event_id,<br>  user_id,<br>  event_name,<br>  event_timestamp,<br>  ingestion_timestamp<br>FROM raw_clickstream_events<br>QUALIFY ROW_NUMBER() OVER (<br>  PARTITION BY event_id<br>  ORDER BY ingestion_timestamp ASC, TO_HEX(MD5(TO_JSON_STRING(raw_clickstream_events))) ASC<br>) = 1</pre><p>Two different cases are worth separating here. If two rows are identical across <strong>every column</strong> (event_id, ingestion_timestamp, everything), the simplest fix is just SELECT DISTINCT — if the rows are truly identical, DISTINCT collapses them into one, no hash-based tiebreaker needed.</p><p>The MD5(TO_JSON_STRING(...)) approach above is for a different case: when event_id and ingestion_timestamp match but there are small differences in <strong>other columns</strong> (e.g. the client fills in a metadata field slightly differently each time it sends the event). Since the rows aren&#39;t fully identical here, DISTINCT can&#39;t deduplicate them — both remain &quot;different&quot; rows. Using the hash as a secondary sort key makes the outcome deterministic even in this case; the goal is simply that the result stays the same every time the query runs.</p><h4><strong>Result</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*y6u3A0dGOHmp1-NCQ8Vr2g.png" /></figure><h3>Check 2: Missing / Null Required Parameters</h3><h4><strong>Why It Matters</strong></h4><p>In a page_view event, the page_url field is required — without it, no page-level breakdown is possible. But a bug in a client version can sometimes send this field empty. These rows don&#39;t get deleted, they just sit there as &quot;unknown page,&quot; and missing data quietly accumulates in page-level funnel reports — since the total event count still looks correct, the problem can go unnoticed for a long time.</p><h4><strong>Sample Data</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4qPNxS0qwXjywt0kuuevpA.png" /></figure><p>Worth noting: page_url actually matters for every event type — knowing which page an add_to_cart came from (a product page? search results?) is important for behavioral analysis. What&#39;s genuinely event-specific here is product_id — it&#39;s only meaningful for product-related events (add_to_cart, purchase) and has no equivalent in page_view. So the reason to check per event type isn&#39;t &quot;page_url isn&#39;t needed&quot; — it&#39;s that each event type has its own additional required fields; general fields like page_url are checked across all event types, while event-specific fields like product_id are only checked for the relevant event type.</p><h4><strong>Detection Query</strong></h4><pre>SELECT<br>  event_name,<br>  COUNT(*) AS total_events,<br>  COUNTIF(page_url IS NULL) AS missing_page_url,<br>  COUNTIF(event_name = &#39;add_to_cart&#39; AND product_id IS NULL) AS missing_product_id<br>FROM raw_clickstream_events<br>GROUP BY event_name</pre><p>missing_page_url is checked with a single rule across all event types, while missing_product_id is a conditional count applied only to add_to_cart rows. The same query pattern can be extended for every additional required-field/event combination and rolled into a single &quot;data quality summary&quot; table.</p><h4><strong>Result</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3cm0TKebpHejOaXnZwiGwg.png" /></figure><p>A 33% missing rate for page_url on page_view, and a missing product_id on add_to_cart — two separate signals, both worth investigating before they hit a dashboard.</p><h3>Check 3: Timestamp Integrity (Late-Arriving Events)</h3><h4><strong>Why It Matters</strong></h4><p>When a user goes offline, events get queued on the device and sent in a batch once the connection returns; in that case, ingestion_timestamp can be hours behind the actual event_timestamp. If metrics like session duration or time between funnel steps are calculated using ingestion_timestamp, the dashboard shows user behavior as much faster or much slower than it actually was.</p><h4><strong>Sample Data</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*K5MHPDk9DGbseRUyf759Kw.png" /></figure><p>Both of u002’s events were sent in a batch roughly 3.5 hours after they actually happened.</p><h4><strong>Detection and Correct Calculation</strong></h4><pre>SELECT<br>  user_id,<br>  event_name,<br>  event_timestamp,<br>  TIMESTAMP_DIFF(ingestion_timestamp, event_timestamp, MINUTE) AS ingestion_delay_minutes,<br>  TIMESTAMP_DIFF(<br>    event_timestamp,<br>    LAG(event_timestamp) OVER (PARTITION BY user_id ORDER BY event_timestamp),<br>    MINUTE<br>  ) AS minutes_since_previous_event<br>FROM raw_clickstream_events</pre><p>The ingestion_delay_minutes column lets you track rows with high delay; the actual session/funnel calculation is always done in event_timestamp order, with ingestion_timestamp kept purely as a technical monitoring field.</p><h4>Result</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Us6_djTvE156PSjxpFQP6w.png" /></figure><p>Even though u002’s delay reaches 3.5 hours, the actual behavioral gap between the two events (4 minutes) is calculated correctly.</p><h3>Check 4: Bot / Invalid Traffic</h3><h4><strong>Why It Matters</strong></h4><p>An empty user agent, a future-dated timestamp, or a user_id in an invalid format are typically signs of bot or corrupted logging. If these rows leak into a dashboard, core metrics like DAU (daily active users) and conversion rate get artificially inflated or deflated — especially if bot traffic concentrates on a specific event (e.g. a scraper that continuously generates page_view), that event&#39;s share diverges from actual user behavior.</p><h4>Sample Data</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ut93h2Ms2gZrkt9Se-7D6Q.png" /></figure><p>The last row has both a NULL user agent and a future date.</p><h4>Single-Source Approach</h4><p>If every analyst writes their own filter separately (one checks user agent, another forgets), the same metric ends up with different numbers across different reports. Instead, the “valid event” definition is centralized in a single view:</p><pre>CREATE OR REPLACE VIEW analytics.valid_clickstream_events AS<br>SELECT<br>  user_id,<br>  event_name,<br>  event_timestamp,<br>  user_agent<br>FROM raw_clickstream_events<br>WHERE user_agent IS NOT NULL<br>  AND event_timestamp &lt;= CURRENT_TIMESTAMP()<br>  AND REGEXP_CONTAINS(user_id, r&#39;^u[0-9]+$&#39;)</pre><p>All dashboards and reports are fed from this one view; the filter logic gets updated in a single place, and every report is guaranteed the same result.</p><h4>Result</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*W99r5_WOZSsMV7WpeYcIYw.png" /></figure><p>The bot row gets excluded because it violates two of the three conditions (empty user agent, future timestamp).</p><p>The three rules used here (empty user agent, future timestamp, invalid user_id format) are the most basic and cheapest detection methods; real bot traffic can show up in far more varied patterns. For example: an unrealistic number of events per second from the same user_id (rate-based detection), known bot/crawler signatures in the user agent string (bot, spider, headless), or an event stream arriving at unnaturally regular intervals, lacking the natural irregularity of human behavior (behavioral pattern). In more mature setups, these rules are typically backed by a third-party bot-scoring service (e.g. reCAPTCHA Enterprise, an IP reputation service); the view-based approach here should be thought of as the minimal filter set you can apply without any additional service.</p><h3>Check 5: Abnormal Deviation in Event Volume</h3><h4>Why It Matters</h4><p>When an SDK (the tracking library embedded in a mobile or web app that captures events and sends them to the server) update breaks, or a tracking tag gets accidentally removed, a specific event’s daily volume can drop sharply compared to the previous day. The problem is, if that drop represents a small share of total event volume, the overall numbers on the dashboard still look “normal” — but the funnel step or metric that depends on that event keeps being calculated incorrectly, silently.</p><h4>Sample Data: daily_event_counts</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nT4drICMtmbK7xS6D0cbjw.png" /></figure><p>The add_to_cart event dropped by ~80% between January 9 and 10 — likely a tracking bug on the &quot;add to cart&quot; button.</p><h4>Detection Query</h4><pre>WITH daily_counts AS (<br>  SELECT event_date, event_name, COUNT(*) AS event_count<br>  FROM raw_clickstream_events<br>  GROUP BY event_date, event_name<br>)<br>SELECT<br>  event_date,<br>  event_name,<br>  event_count,<br>  LAG(event_count) OVER (<br>    PARTITION BY event_name ORDER BY event_date<br>  ) AS previous_day_count,<br>  ROUND(<br>    SAFE_DIVIDE(<br>      event_count - LAG(event_count) OVER (PARTITION BY event_name ORDER BY event_date),<br>      LAG(event_count) OVER (PARTITION BY event_name ORDER BY event_date)<br>    ) * 100, 1<br>  ) AS pct_change<br>FROM daily_counts<br>QUALIFY ABS(pct_change) &gt; 30</pre><p>LAG() calculates the percentage change compared to the previous day; QUALIFY ABS(pct_change) &gt; 30 lists only deviations (drops or spikes) greater than 30%. This threshold isn&#39;t fixed — it should be calibrated to the event&#39;s normal day-to-day fluctuation range.</p><h4>Result</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*I6BpVYJYjXICVNY2xGEuwA.png" /></figure><p>This row gets flagged automatically, triggering investigation before it reaches the dashboard.</p><h4>Daily or Weekly Comparison?</h4><p>Comparing only against the previous day can easily produce false positives from weekly seasonality: weekend traffic can naturally be lower than weekday traffic, which could show up as an “abnormal” spike on Monday morning compared to Sunday. Two approaches make more sense to avoid this:</p><ul><li><strong>Week-over-week comparison:</strong> Compare against the same day last week instead of the previous day (going back 7 rows with LAG(event_count, 7)) — comparing this Monday to last Monday accounts for the weekly cycle.</li><li><strong>Rolling average baseline:</strong> Compare against the average of the last 7 days instead of a single day, which reduces the chance of a single day’s fluctuation falsely triggering the threshold.</li></ul><p>In practice, using both together gives the most robust result: compare against both the previous day (to catch sudden, short-term breaks) and the same day last week (to filter out seasonal deviations), and flag only when both exceed the threshold.</p><h3>Summary: The Checklist Before You Trust a Dashboard</h3><ol><li><strong>Duplicate event check</strong> — deduplicating each event_id with QUALIFY ROW_NUMBER() ... = 1</li><li><strong>Missing required parameters</strong> — measuring the missing-field rate per event type with COUNTIF(... IS NULL)</li><li><strong>Timestamp integrity</strong> — running session/funnel calculations on event_timestamp, using ingestion_timestamp purely for delay monitoring</li><li><strong>Bot/invalid traffic filter</strong> — centralizing the “valid event” definition in a single view</li><li><strong>Abnormal deviation detection</strong> — comparing daily event volume to the previous day with LAG() and flagging deviations above the threshold</li></ol><p>These five checks are a minimum set you can run before a dashboard goes live. In the next article, I’ll cover how I automated the query optimization and data validation processes from the previous two articles using AI.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f96019e7d83c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Optimizing Cost and Performance in BigQuery with Window Functions]]></title>
            <link>https://medium.com/@zeyneepyilmaz12/optimizing-cost-and-performance-in-bigquery-with-window-functions-4791be1a0176?source=rss-d444764fee49------2</link>
            <guid isPermaLink="false">https://medium.com/p/4791be1a0176</guid>
            <category><![CDATA[ecommerce]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[cost-optimization]]></category>
            <category><![CDATA[sql-window-functions]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Zeynep Yılmaz]]></dc:creator>
            <pubDate>Sat, 05 Sep 2026 14:28:44 GMT</pubDate>
            <atom:updated>2026-09-05T14:31:40.507Z</atom:updated>
            <content:encoded><![CDATA[<p><em>Based on real funnel analysis scenarios from e-commerce and travel platforms</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*N-qG96bHsoReP1B-9D8yZA.png" /></figure><h3>Introduction</h3><p>One of the most common issues when writing BigQuery queries is producing the correct result while scanning far more data than necessary. This is especially true in user behavior and funnel analyses, where self-joins, correlated subqueries, and GROUP BY + join combinations tend to scale in cost and runtime far faster than the underlying data volume.</p><p>In this article, I walk through three recurring funnel-analysis scenarios, first solved without window functions and then with them, including sample data and result tables at each step.</p><h3>Problem 1: Finding a User’s Previous Step</h3><blockquote><strong>Use Case</strong></blockquote><p>In a checkout funnel, we want to see how long users spend moving from one step to the next, and which step follows which. For example, if the time between “add to cart” and “enter payment info” is unusually long, that step likely has a UX friction point worth investigating.</p><blockquote>Table Schema: funnel_events</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wCDN3YKT37612ymZ9UgcLA.png" /></figure><blockquote><strong>Sample Data</strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*IXHvB09NZEEHCGngHdHMhg.png" /></figure><p>u001 completed the full funnel, u002 stalled at the payment step, and u003 never moved past adding an item to the cart.</p><blockquote><strong>Without a Window Function</strong></blockquote><pre>SELECT<br>  a.user_id,<br>  a.event_name,<br>  a.event_timestamp,<br>  b.event_name AS previous_event<br>FROM funnel_events a<br>LEFT JOIN funnel_events b<br>  ON a.user_id = b.user_id<br>  AND b.event_timestamp = (<br>    SELECT MAX(event_timestamp)<br>    FROM funnel_events c<br>    WHERE c.user_id = a.user_id<br>      AND c.event_timestamp &lt; a.event_timestamp<br>  )</pre><p>On top of the self-join, a correlated subquery runs here: for every row, the nearest previous timestamp for that user is computed separately. As the table grows, the amount of data scanned increases combinatorially rather than linearly.</p><blockquote><strong>With a Window Function</strong></blockquote><pre>SELECT<br>  user_id,<br>  event_name,<br>  event_timestamp,<br>  LAG(event_name) OVER (<br>    PARTITION BY user_id<br>    ORDER BY event_timestamp<br>  ) AS previous_event,<br>  TIMESTAMP_DIFF(<br>    event_timestamp,<br>    LAG(event_timestamp) OVER (PARTITION BY user_id ORDER BY event_timestamp),<br>    SECOND<br>  ) AS seconds_since_previous_step<br>FROM funnel_events</pre><p>The table is read once; PARTITION BY user_id orders rows per user, and LAG fetches the previous row directly. The row explosion caused by the self-join and subquery disappears entirely.</p><blockquote><strong>Result Table</strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*70sS8GGfBOW29-D70zvv_Q.png" /></figure><p>Each user’s first event naturally returns NULL for previous_event; thanks to PARTITION BY, one user&#39;s event never leaks into another user&#39;s row.</p><h3>Problem 2: Finding Each User’s Last Step</h3><blockquote><strong>Use Case</strong></blockquote><p>We want to see where users who abandoned the funnel during a given period dropped off. To do this, we find each user’s most recent event within that period; if that last event isn’t a purchase, the user is considered to have abandoned the funnel.</p><blockquote><strong>Table Schema: </strong><strong>funnel_events</strong></blockquote><p>Same schema as in Problem 1.</p><blockquote><strong>Sample Data</strong></blockquote><p>The same sample data from Problem 1 applies: u001 completed all three steps, u002 stalled at the payment step, and u003 only added an item to the cart.</p><blockquote><strong>Without a Window Function</strong></blockquote><pre>WITH last_event_time AS (<br>  SELECT<br>    user_id,<br>    MAX(event_timestamp) AS last_ts<br>  FROM funnel_events<br>  WHERE event_date BETWEEN &#39;2025-01-01&#39; AND &#39;2025-01-31&#39;<br>  GROUP BY user_id<br>)<br>SELECT<br>  f.user_id,<br>  f.event_name AS last_step,<br>  f.event_timestamp<br>FROM funnel_events f<br>JOIN last_event_time l<br>  ON f.user_id = l.user_id<br>  AND f.event_timestamp = l.last_ts</pre><p>This approach requires two passes: first a GROUP BY to find each user&#39;s last timestamp, then a join back to the main table. If a user has more than one event at the exact same timestamp, the join can also produce duplicate rows — a case that needs to be handled separately.</p><blockquote><strong>With a Window Function</strong></blockquote><pre>WITH ranked_events AS (<br>  SELECT<br>    user_id,<br>    event_name,<br>    event_timestamp,<br>    ROW_NUMBER() OVER (<br>      PARTITION BY user_id<br>      ORDER BY event_timestamp DESC<br>    ) AS rn<br>  FROM funnel_events<br>  WHERE event_date BETWEEN &#39;2025-01-01&#39; AND &#39;2025-01-31&#39;<br>)<br>SELECT user_id, event_name AS last_step, event_timestamp<br>FROM ranked_events<br>WHERE rn = 1</pre><p>This ranks and selects in a single pass, with no join required. In the case of tied timestamps, adding a secondary sort key (e.g. event_id) to ROW_NUMBER() also lets you control duplicate risk — an extra layer of flexibility the window function approach provides.</p><p>The key detail is the WHERE event_date BETWEEN ... filter. If the table is partitioned by event_date, BigQuery only scans the relevant partitions — true for both approaches, but since the window function version has no added join cost, its total scan is smaller.</p><blockquote><strong>Result Table</strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XQXZVHZkaFjYkNLBymHneQ.png" /></figure><p>This table directly shows the drop-off distribution: u002 left at the payment info step, while u003 left right after adding an item to the cart.</p><h3>Problem 3: Measuring Cumulative Change Over Time</h3><blockquote><strong>Use Case</strong></blockquote><p>We want to know how cart contents evolve over time: as a user adds and removes items, what does the running total in their cart look like, and what’s the trend over their last few interactions? This kind of metric helps distinguish users whose carts steadily grow toward a purchase from users whose carts fluctuate and are eventually abandoned.</p><blockquote><strong>Table Schema: </strong><strong>cart_events</strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SHMZeE4I7auX0nuppyYnVg.png" /></figure><blockquote><strong>Sample Data (3 Users)</strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yC_4zynxa9bRetZqTng1WQ.png" /></figure><p>Here items_in_cart isn&#39;t an absolute count but a delta per interaction: u001 first adds 1 item, then adds 2 more, then removes 1.</p><blockquote><strong>Without a Window Function</strong></blockquote><pre>SELECT<br>  c1.user_id,<br>  c1.event_timestamp,<br>  c1.items_in_cart,<br>  (<br>    SELECT SUM(c2.items_in_cart)<br>    FROM cart_events c2<br>    WHERE c2.user_id = c1.user_id<br>      AND c2.event_timestamp &lt;= c1.event_timestamp<br>  ) AS running_cart_total<br>FROM cart_events c1</pre><p>This query runs a separate subquery for every single row: for a user with N rows of history, that works out to roughly O(N²) scanning in practice. It goes unnoticed on small datasets, but cost climbs fast on a high-traffic cart table.</p><blockquote><strong>With a Window Function</strong></blockquote><pre>SELECT<br>  user_id,<br>  event_timestamp,<br>  items_in_cart,<br>  SUM(items_in_cart) OVER (<br>    PARTITION BY user_id<br>    ORDER BY event_timestamp<br>    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW<br>  ) AS running_cart_total,<br>  AVG(items_in_cart) OVER (<br>    PARTITION BY user_id<br>    ORDER BY event_timestamp<br>    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW<br>  ) AS moving_avg_last_3_events<br>FROM cart_events</pre><p>The same result is computed in a single pass, without triggering a separate subquery per row. Explicitly defining the ROWS BETWEEN frame matters both for correctness and readability — relying on the default frame is a common mistake that can produce the wrong windowed calculation in funnel analyses.</p><blockquote><strong>Result Table</strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GpodhFbN8zQwKAMv6RwbyQ.png" /></figure><p>running_cart_total is the net item count accumulated up to that point; moving_avg_last_3_events averages over however many events actually exist so far for users who don&#39;t yet have 3 (e.g. u001&#39;s first row) — a direct result of PARTITION BY keeping the window bounded to each user.</p><h3>Cost Optimization: Beyond the Window Function</h3><p>Moving queries to window functions isn’t enough on its own. The real cost and performance gain comes from combining that approach with three additional practices:</p><ul><li><strong>Partitioning and clustering strategy:</strong> Partitioning event tables by event_date and clustering by user_id reduces the data volume scanned by both window functions and filter conditions.</li><li><strong>Early filtering:</strong> The WHERE clause should be applied as early as possible, ideally in the very first CTE. Window functions process all rows before any filter is applied, so delaying the filter adds unnecessary cost.</li><li><strong>Avoiding unnecessary </strong><strong>SELECT *:</strong> Selecting only the columns you actually need reduces the bytes scanned directly, given BigQuery&#39;s columnar storage format.</li></ul><p>Applying these three practices to a “Checkout” data pipeline produced a measurable improvement in both query cost and data accuracy, while also simplifying the pipeline by removing redundant intermediate tables.</p><h3>Conclusion</h3><p>All three examples share the same pattern: queries written without window functions rely either on multi-step join/subquery chains or on repeated per-row calculations. Window functions collapse that chain into a single pass, reducing both query complexity and the query’s share of BigQuery’s slot- and byte-based cost model.</p><p>In the next article, I’ll cover how to build a reliable clickstream validation and log-cleaning process on top of this clean event data — because no matter how well-written a window function is, the result is only as trustworthy as the source data feeding it.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4791be1a0176" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[UP SCHOOL — DATA ANALYSIS YOLCULUĞUM]]></title>
            <link>https://medium.com/@zeyneepyilmaz12/up-school-data-analysis-yolculu%C4%9Fum-862e0cc14d1e?source=rss-d444764fee49------2</link>
            <guid isPermaLink="false">https://medium.com/p/862e0cc14d1e</guid>
            <category><![CDATA[up-school]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Zeynep Yılmaz]]></dc:creator>
            <pubDate>Sun, 06 Mar 2022 18:51:59 GMT</pubDate>
            <atom:updated>2022-03-06T18:51:59.354Z</atom:updated>
            <content:encoded><![CDATA[<h3>UP SCHOOL — DATA ANALYSIS YOLCULUĞUM</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MAYqw5M740PgnlMQ-AVIdg.jpeg" /></figure><p>Bazen hayalleriniz ve amaçlarınız olsa da içinizde ki ışığı ve yetenekleri görmekte zorlanıyor insan. Ben hayatım boyunca bir amacım olsun istedim. Her zaman sorguladım kendimi. “<em>Ne yapmak istiyorum?”</em> sorusunu<em> </em>sordum kendime. İşte böyle bir boşluk içerisinde iken yolum UP School ekibiyle kesişti. Başlarda sadece teknik bir eğitim alacağımı düşünmüştüm. Ama aslında çok yanılmıştım. Artık hedeflerini bilen ve sorduğum sorular ile başa çıkmayı bilen güçlü bir kadın olarak gelin sizlere UP School maceramdan ve bana neler kattığından bahsedeyim.</p><p>Benim bu alana olan merakım son staj zamanımda başlamıştı. Takım liderimin benim içimdeki ışığı görmesi ile kendi başıma kurslar alarak kendimi geliştirmeye başladım. Ama herkes gibi yine de nereden başlayacağımı nasıl bu alana sonradan dahil olup kendimi kanıtlayacağımı kestiremiyordum. Belirsizliğin içerisinde iken en doğru kararı alıp UP School ekibine katıldım.</p><p>Ve kısa süre sonra UP School Data Analysis eğitimlerimiz büyük bir hızla başladı. Hard skill dersleri ile Python, NumPy kütüphanesi, Pandas kütüphanesi ile Veri Manipülasyonu, Scikit-Learn ile İstatistik ve Makine Öğrenimi, SQL gibi konularda kendimizi geliştirdik. Hafta da bir live coding dersleri ile canlı olarak pratikler gerçekleştirdik. Ve derslerimizden sonra yapmamız gereken görevler ile bilgilerimizi pekiştirdik.</p><p>Şu an düşündüğümde çok kolay gelen ama eğitim başlarında çok da zorlandığım dersler oldu. Pes etmeye yeltendiğim zamanlar bile oldu. Ama işte tam da bu zamanlarda eğitim boyunca pes etmememiz için bizleri destekleyen UP School ekibi oldu. Her hafta düzenlenen soft skill derslerinde yapılan seyirler ve mentor yayınları ile başarı hikayelerine tanıklık ettik. İlham verici konuşmalar ile hayallerimizi daha da güçlendirdik.</p><p>Bir diğer destekçilerim ise eğitimi beraber aldığımız arkadaşlarım oldu. Aslında sanal ekranlar ardından birbirlerine bakan yabancı insanlardık. Çok yorulduk zaman zaman da çok zorlandık ama her pes ettiğimizde birbirimizi sımsıkı tuttuk. Ekranların ardından birbirlerine bakan yabancılar iken bir baktık en büyük destekçilerimiz olduk.</p><p>UP School kültüründe öğrenmiş olduğum ve hayatım boyunca aklımdan çıkarmayacak olduğum bahsetmek istediğim bir önemli konu daha var. Sizlere bir soru sormak istiyorum.</p><p>“<em>En son kendinizi ne zaman takdir ettiniz?</em> ”</p><p>Bana ilk bu soru sorulduğunda cevap bile verememiştim. Yani kendimi takdir etmekten o kadar uzaktım. Ama şunu öğrendim. Aslında kendinizi takdir etmenizde göz etmeniz gereken en önemli metot;</p><p>“<em>Dünya için küçük kendim için büyük bir adım.</em>”</p><p>Hala daha bu konuda kendimi geliştirmeye çalışıyorum. Örneğin fitness yapmak istiyordum. Ama sürekli bahaneler ile erteliyordum. Ama yine bir UP School seyirinden sonra cesaretlenerek bu hafta kesin başlayacağım diyerek başladım. Ve kendimi bu nedenle takdir ettim. Veya bu sizler için uzun zamandır ertelediğiniz bir kitabı okumaya başlamak da olabilir. Yaptığınız şey küçük bile olsa kendinizi takdir edin. Kimine göre küçük adımlar olabilir ama sizler için çok büyük adımlar olduklarına eminim. 😊</p><p>Sizlere verebileceğim son ve en önemli tavsiye ise hayatınızda önemli amaçlar ile başladığınız maceralarda sizler ile aynı yolda adım atan insanların elinden tutun. O zaman bitemeyecek gibi düşündüğünüz yollar başarılı bir şekilde kat edilmiş olur. Ben ve arkadaşlarım bu yolculuğumuzu bitirdik.</p><p>Şimdi dönüp kendime baktığımda daha güçlü bir kadın görüyorum. Artık isteklerini, hayata bakış açısını daha güzel ifade eden bir kadın var. Ve kendimi olduğum gibi sevebiliyorum. Önemli olan da bu bence.</p><p>Teknolojide yetişen rol model kadınlar için elimden ne geliyorsa yapmak istiyorum. Eğer sizlerin de attığınız adımlarda aklınızda olan sorular olursa her zaman yardımcı olmak isterim. Bana <a href="https://www.linkedin.com/in/zeynep-yillmaz/">buradan</a> ulaşabilirsiniz.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rtPcdPwqO7oPk-nz4r3cOQ.jpeg" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=862e0cc14d1e" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>