<?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 William Song on Medium]]></title>
        <description><![CDATA[Stories by William Song on Medium]]></description>
        <link>https://medium.com/@williamwnsong?source=rss-4588b3533f10------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*ip0g-wqSjr99yaBDwWrk3g.jpeg</url>
            <title>Stories by William Song on Medium</title>
            <link>https://medium.com/@williamwnsong?source=rss-4588b3533f10------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 09:26:00 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@williamwnsong/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[How to Calculate Retention Rate 留存率分析]]></title>
            <link>https://medium.com/@williamwnsong/how-to-calculate-retention-rate-%E7%95%99%E5%AD%98%E7%8E%87%E5%88%86%E6%9E%90-48f03dd19014?source=rss-4588b3533f10------2</link>
            <guid isPermaLink="false">https://medium.com/p/48f03dd19014</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[retention-rate]]></category>
            <category><![CDATA[留存率]]></category>
            <category><![CDATA[retention]]></category>
            <category><![CDATA[bigquery]]></category>
            <dc:creator><![CDATA[William Song]]></dc:creator>
            <pubDate>Fri, 03 Nov 2023 04:18:49 GMT</pubDate>
            <atom:updated>2023-11-08T02:47:25.929Z</atom:updated>
            <content:encoded><![CDATA[<p>Retention Rate is one of the major KPI metrics in many businesses. The concept isn’t too complicated, but it involves many details and is important to help you to understand how your business perform. It is also the prerequisite information to further calculate Customer Lifetime Value, known as CLV or LTV. In this long post, I will split the contents in three sections: Evaluation Steps, Charts, and SQL codes. Hopefully it will be easier for readers to digest.</p><h3><strong>EVALUATION STEPS</strong></h3><p>The major steps are as following:</p><ol><li>Identify the “cohort”, and the cohort here could be a group of people who made purchases through the same marketing campaign/event, or a group of people who subscribed services in certain time period (e.g.// same day or same month). <br>Depending on the business you are in, the challenge is that all people in all cohorts have different purchasing/revisiting cycles, so defining your cohort is important as the first step. Usually it is either event-based or time-based, but sometimes there are other categorical variables would be added to narrow down cohort groups. Keep in mind that the more variables added in evaluation, the more complicate the SQL codes and results could be. <br>Using gaming industry as an example, we usually use player signup date as cohort base (time-based). Each day, there are some number of people joining the game, and each day is a different cohort. The retention rate is evaluated day by day. But it is possible to add other variable, for example, which countries cohorts belong to, which marketing campaigns cohorts join from, are they payer or non-payer, and these additional variables will make SQL codes more complex to write and read. (I will explain later in the SQL section.)</li><li>Understand the math formula, and it’s easy:<br>(# of people in the same cohort who revisited during period / # of people in the same cohort at start of period) * 100<br>Although it seems easy, the challenge part is calculating the rate for each cohort start from each of their initial day to each of their future days. For example, on the first day July 1st, we have 100 people joining the business, and usually is labelled as day-1. (Note that some people choose day-0 as the label for the very first day, and the very first day is supposed to have 100% rate as the base, but usually is omitted in charts. From other people’s work, you might see day-1 label with values less than 100%, and if no specific reason, it usually means they use day-0 label, and day-1 is the next day value. It’s a personal preference on choosing the term for the very first day label. I prefer day-1.)<br>In the following day July 2nd, under the same group, there are 50 people revisit, labelled as day-2, and the retention rate is 50/100 = 50%. <br>The next day July 3rd for the same group, day-3, 20 people revisit, and the retention rate is 20/100 = 20%, and so on for future days. Keep in mind that it is the people in the same cohort that joined on July 1st. There would be a new cohort of people who join the business on July 2nd, and it is considered its own new cohort, and July 3rd is the new cohort’s day-2 and so on. So you can imagine, as each new day being added, the volume of data and calculation will increase fast.<br>By the way, “Churn Rate” is the reverse of Retention Rate, which is 1 -Retention Rate. Churn Rate is another common term you probably will see when working on Retention Rate.</li><li>From the results in step2, we can use visualization tools to create charts to find out the results. In my experience, charts to display retention rate are not easy to digest at first glance. You will have to practice and get use to, and often teach your stakeholders on how to read the charts as well. There are three major charts used to show the outcome of retention rate. All 3 are popular, but with their downsides, and I will demonstrate them below.</li></ol><h3>CHARTS</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MAtZXiDTnuXIpelPa6y-xQ.png" /><figcaption>Source: <a href="https://help.peelinsights.com/docs/retention-rate-vs-repurchase-rate-1">https://help.peelinsights.com/docs/retention-rate-vs-repurchase-rate-1</a></figcaption></figure><p>Based on the calculation in Step2, the first popular chart (above) is a stair-like tabular chart. Each row represents a cohort, and each column and data value within represents the retention rate for each following day or period. Usually we use heat-map colours in the tabular chart to help reading the numbers, otherwise it’s relatively messy with a lot of numbers.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sBc6-CwuFZz9W9AVQ1V-8w.jpeg" /><figcaption>Source: <a href="https://segment.com/growth-center/customer-retention/rate/">https://segment.com/growth-center/customer-retention/rate/</a></figcaption></figure><p>The second type (above) is a down-slope line chart which is transformed from tabular chart. Each line represents a cohort and its retention rate for each following day or defined period. Note that the cohort defined in this chart is by their app genres (event-based) but not some joining date. So it is important to clearly identify your cohort definition before running analysis. The downside of the chart is that when you get more and more cohorts in your data, there will be too many lines overlapping each other and becomes challenging to read. That’s where the third type of chart comes in.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CkBGbKctn1qWTen9a3kZXg.png" /><figcaption>Source: <a href="https://www.linkedin.com/pulse/how-measure-retention-173tech/">https://www.linkedin.com/pulse/how-measure-retention-173tech/</a></figcaption></figure><p>The third type (above) provides information on long term performance base on the day-n line we choose in the Viz design. I personally prefer this chart to demonstrate the retention information to stakeholders. The way to read this chart is to treat each line as day-n. For example the blue line is Day-1, the red line (Week1) is Day-7, and the green line (Month1) is Day-30. The blue line here simply tells us the Day-1 retention rates among different cohorts. And vertically, each group on the x-axis is a cohort. Using 2020–03 as an example, 2020–03 is a cohort group, and vertically the data point on the blue line is about 80.0% represents its Day-1 retention rate. The data point on the red line is about 65.0%, is its Day-7 retention rate. The data point on the green line is about 55.0%, is its Day-30 retention rate. Therefore, vertically, each data point among different lines under the same cohort tells the retention performance in such cohort. In this chart, we can probably conclude that 2020–03 cohort is healthier than other cohorts before it (between 2019–07 and 2020–02) because this cohort has better Day-1, Day-7, Day-30 retention rates than other earlier cohorts. The challenge for reading this chart is that it’s usually confusing at beginning to check cohorts vertically. But once you get used to it, it’s very easy to compare the retention performance between different cohorts.</p><h3>SQL (written in Google BigQuery Environment)</h3><p>I usually design the SQL queries into two sections. The Section1 is to build the base table that includes necessary variables, and usually is saved in a temporary table for later use. Then I can play around and try different query setups in Section2 without re-run the Section1 queries and waste reading resources. But feel free to combine query codes from all sections together if you already know what you want.</p><p>Simple Version — Section1 (time-based cohorts)</p><pre>-- save the result to a table for later use<br>truncate table `mid_retention_data`;<br>insert into `mid_retention_data`<br><br>with join_date as (<br>-- find out each user&#39;s first join date, each user should have 1 row only<br>    select user_id<br>        , user_join_date<br>    from `user_metric_table`<br>)<br>, log_date as (<br>-- find out each user&#39;s all login dates<br>-- eg// a user visits and revisits for 30 days then there are 30 rows<br>-- the table size will be large if long period and millions of users<br>    select distinct user_id<br>        , login_date<br>    from `user_login_data`<br>)<br>-- join two tables together and calculate the period apart<br>-- between each login date and first date<br>select distinct b.user_id<br>    , b.user_join_date<br>    , a.login_date<br>-- their first login date should be the same as join date<br>-- use date_diff() to calculate day-n information<br>-- + 1 or not depends on you want the first day as 0 or 1, and I prefer 1<br>    , date_diff(a.login_date, b.user_join_date, day) + 1 as day_num<br>from log_date as a<br>inner join join_date as b on a.user_id = b.user_id<br>-- we can use order by to briefly check if results meet expectation<br>-- order by b.user_id, a.login_date<br>;</pre><p>Complex Version — Section1 (time-based cohorts)</p><pre>-- save the result to a table for later use<br>truncate table `mid_retention_data`;<br>insert into `mid_retention_data`<br><br>with join_date as (<br>-- find out each user&#39;s first join date, more variables add complexity<br>-- each user should have 1 row only<br>    select user_id<br>        , user_join_date<br>-- you can add all potential categorical variables first<br>-- then in Section2, you are free to select desired ones only<br>        , case<br>            when campaign_id in (123, 456, 789) then &#39;special&#39;<br>            when campaign_id in (100, 200) then &#39;organic&#39;<br>            else &#39;regular&#39;<br>          end as campaign_type<br>        , case <br>            when country = &#39;KR&#39; then &#39;korea&#39;<br>            when country = &#39;JP&#39; then &#39;japan&#39;<br>            when country = &#39;US&#39; then &#39;us&#39;<br>            else &#39;others&#39;<br>          end as country_others_vs_tier1<br>        , case <br>            when all_iap_amount &gt; 0 then &#39;payer&#39;<br>            else &#39;non_payer&#39;<br>          end as payer_flag<br>    from `user_metric_table`<br>)<br>, log_date as (<br>-- find out each user&#39;s all login dates<br>-- eg// a user visits and revisits for 30 days then there are 30 rows<br>-- the table size will be large if long period and millions of users<br>    select distinct user_id<br>        , login_date<br>    from `user_login_data`<br>)<br>-- join two tables together and calculate the period apart<br>-- between each login date and first date<br>select distinct b.user_id<br>    , b.user_join_date<br>    , b.campaign_type<br>    , b.country_others_vs_tier1<br>    , b.payer_flag<br>    , a.login_date<br>-- their first login date should be the same as join date<br>-- use date_diff() to calculate day-n information<br>-- + 1 or not depends on you want the first day as 0 or 1, and I prefer 1<br>    , date_diff(a.login_date, b.user_join_date, day) + 1 as day_num<br>from log_date as a<br>inner join join_date as b on a.user_id = b.user_id<br>-- we can use order by to briefly check if results meet expectation<br>-- order by b.user_id, a.login_date<br>;</pre><p>In the queries above, you can use Simple Version to test your own data. In the Complex Version, I have added some additional variables in the complex version such as campaign_type, country, and payer flag information. These will increase the complexity in the following sections. On the other hand, since I save the results to a middle table, I can add as many potentially needed categorical variables and only select part of them in Section2. Also, until this stage, after running a short chunk of queries below, you can technically export the data to Viz tools like Tableau or Power BI, but, with a pitfall.</p><pre>-- using simple version as an example<br>select day_num<br>  , user_join_date -- represent cohorts<br>  , login_date<br>  , count(distinct user_id) as user_counter<br>from last_chuck_query_above<br>group by day_num, user_join_date, login_date<br>order by user_join_date, day_num<br>;</pre><p>The data result from the queries above provides you the most flexibility to design your charts in Viz tools. You can simply add some division calculations in your tools and you will find out the retention rate for each cohort on day-n. This is where I would stop building queries if my data size is small. However, if you work in a Big Data environment, it is highly likely that the result still contains million rows of data, which is impractical and not the best practice to directly export to Viz tools.<br>So with few more steps in the follow section, we will narrow down the result from millions to few hundreds or few thousands of rows.</p><p>Simple Version — Section2 (time-based cohorts)</p><pre>with base_table as (<br>    select distinct user_id<br>        , user_join_date<br>        , day_num<br>    from `mid_retention_data` -- saved middle table from Secion1<br>)<br>, data_per_day as (<br>    select user_join_date -- represent cohorts<br>-- add or remove day_n based on your requirements in lines below<br>-- these calculate the retained user counts on day_n for each cohort<br>-- day_1 is the first day for each cohort<br>        , sum(case when day_num = 1 then 1 else 0 end) as day_1<br>        , sum(case when day_num = 2 then 1 else 0 end) as day_2<br>        , sum(case when day_num = 3 then 1 else 0 end) as day_3<br>        , sum(case when day_num = 4 then 1 else 0 end) as day_4<br>        , sum(case when day_num = 5 then 1 else 0 end) as day_5<br>        , sum(case when day_num = 6 then 1 else 0 end) as day_6<br>        , sum(case when day_num = 7 then 1 else 0 end) as day_7<br>        , sum(case when day_num = 14 then 1 else 0 end) as day_14<br>        , sum(case when day_num = 30 then 1 else 0 end) as day_30<br>        , sum(case when day_num = 90 then 1 else 0 end) as day_90<br>        , sum(case when day_num = 183 then 1 else 0 end) as day_183<br>        , sum(case when day_num = 365 then 1 else 0 end) as day_365<br>    from base_table<br>    group by user_join_date<br>)<br>, result_data as (<br>    SELECT user_join_date -- represent cohorts<br>        , CASE WHEN day_1 IS NOT NULL THEN day_1 ELSE 0 END as d1<br>-- add or remove dn based on your requirements in lines below<br>-- these calculate the retention rate on dn for each cohort<br>-- day_1 is the denominator for following days (retention rate formula)<br>        , CASE WHEN day_1 IS NOT NULL THEN <br>            SAFE_DIVIDE(day_2, day_1) ELSE 0 END as d2<br>        , CASE WHEN day_1 IS NOT NULL THEN <br>            SAFE_DIVIDE(day_3, day_1) ELSE 0 END as d3<br>        , CASE WHEN day_1 IS NOT NULL THEN <br>            SAFE_DIVIDE(day_4, day_1) ELSE 0 END as d4<br>        , CASE WHEN day_1 IS NOT NULL THEN <br>            SAFE_DIVIDE(day_5, day_1) ELSE 0 END as d5<br>        , CASE WHEN day_1 IS NOT NULL THEN <br>            SAFE_DIVIDE(day_6, day_1) ELSE 0 END as d6<br>        , CASE WHEN day_1 IS NOT NULL THEN <br>            SAFE_DIVIDE(day_7, day_1) ELSE 0 END as d7<br>        , CASE WHEN day_1 IS NOT NULL THEN <br>            SAFE_DIVIDE(day_14, day_1) ELSE 0 END as d14<br>        , CASE WHEN day_1 IS NOT NULL THEN <br>            SAFE_DIVIDE(day_30, day_1) ELSE 0 END as d30<br>        , CASE WHEN day_1 IS NOT NULL THEN <br>            SAFE_DIVIDE(day_90, day_1) ELSE 0 END as d90<br>        , CASE WHEN day_1 IS NOT NULL THEN <br>            SAFE_DIVIDE(day_183, day_1) ELSE 0 END as d183<br>        , CASE WHEN day_1 IS NOT NULL THEN <br>            SAFE_DIVIDE(day_365, day_1) ELSE 0 END as d365<br>    FROM data_per_day<br>)<br>-- unpivot is for viz design so we can use retained_days as filter<br>-- try it yourself with and without unpivot and you will understand why<br>SELECT user_join_date<br>    , d1 as new_user_count<br>    , retained_days<br>    , retention_rate<br>FROM result_data<br>unpivot<br>(<br>    retention_rate<br>    for retained_days in (d2, d3, d4, d5, d6, d7, d14, d30, d90, d183, d365)<br>) unpiv<br>;</pre><p>Complex Version — Section2 (time-based cohorts)</p><pre>with base_table as (<br>    select distinct user_id<br>        , user_join_date -- represent cohorts<br>-- say I stored 10 additional categorical variables in the middle table<br>-- I can only select fewer variables here depending on goals.<br>        , campaign_type<br>        , country_others_vs_tier1<br>        , payer_flag<br>        , day_num<br>    from `mid_retention_data` -- saved middle table from Secion1<br>)<br>, data_per_day as (<br>    select user_join_date-- represent cohorts<br>        , campaign_type<br>        , country_others_vs_tier1<br>        , payer_flag<br>-- add or remove day_n based on your requirements in lines below<br>-- these calculate the retained user counts on day_n for each cohort<br>-- day_1 is the first day for each cohort<br>        , sum(case when day_num = 1 then 1 else 0 end) as day_1<br>        , sum(case when day_num = 2 then 1 else 0 end) as day_2<br>        , sum(case when day_num = 3 then 1 else 0 end) as day_3<br>        , sum(case when day_num = 4 then 1 else 0 end) as day_4<br>        , sum(case when day_num = 5 then 1 else 0 end) as day_5<br>        , sum(case when day_num = 6 then 1 else 0 end) as day_6<br>        , sum(case when day_num = 7 then 1 else 0 end) as day_7<br>        , sum(case when day_num = 14 then 1 else 0 end) as day_14<br>        , sum(case when day_num = 30 then 1 else 0 end) as day_30<br>        , sum(case when day_num = 90 then 1 else 0 end) as day_90<br>        , sum(case when day_num = 183 then 1 else 0 end) as day_183<br>        , sum(case when day_num = 365 then 1 else 0 end) as day_365<br>    from base_table <br>    group by user_join_date, campaign_type, country_others_vs_tier1, payer_flag<br>)<br>, result_data as (<br>    SELECT user_join_date<br>        , campaign_type<br>        , country_others_vs_tier1<br>        , payer_flag<br>        , CASE WHEN day_1 IS NOT NULL THEN day_1 ELSE 0 END as d1<br>-- add or remove dn based on your requirements in lines below<br>-- these calculate the retention rate on dn for each cohort<br>-- day_1 is the denominator for following days (retention rate formula)<br>        , CASE WHEN day_1 IS NOT NULL THEN <br>            SAFE_DIVIDE(day_2, day_1) ELSE 0 END as d2<br>        , CASE WHEN day_1 IS NOT NULL THEN <br>            SAFE_DIVIDE(day_3, day_1) ELSE 0 END as d3<br>        , CASE WHEN day_1 IS NOT NULL THEN <br>            SAFE_DIVIDE(day_4, day_1) ELSE 0 END as d4<br>        , CASE WHEN day_1 IS NOT NULL THEN <br>            SAFE_DIVIDE(day_5, day_1) ELSE 0 END as d5<br>        , CASE WHEN day_1 IS NOT NULL THEN <br>            SAFE_DIVIDE(day_6, day_1) ELSE 0 END as d6<br>        , CASE WHEN day_1 IS NOT NULL THEN <br>            SAFE_DIVIDE(day_7, day_1) ELSE 0 END as d7<br>        , CASE WHEN day_1 IS NOT NULL THEN <br>            SAFE_DIVIDE(day_14, day_1) ELSE 0 END as d14<br>        , CASE WHEN day_1 IS NOT NULL THEN <br>            SAFE_DIVIDE(day_30, day_1) ELSE 0 END as d30<br>        , CASE WHEN day_1 IS NOT NULL THEN <br>            SAFE_DIVIDE(day_90, day_1) ELSE 0 END as d90<br>        , CASE WHEN day_1 IS NOT NULL THEN <br>            SAFE_DIVIDE(day_183, day_1) ELSE 0 END as d183<br>        , CASE WHEN day_1 IS NOT NULL THEN <br>            SAFE_DIVIDE(day_365, day_1) ELSE 0 END as d365<br>    FROM data_per_day<br>)<br>-- unpivot is for viz design so we can use retained_days as filter<br>-- try it yourself with and without unpivot and you will understand why<br>SELECT user_join_date<br>    , campaign_type<br>    , country_others_vs_tier1<br>    , payer_flag<br>    , d1 as new_user_count<br>    , retained_days<br>    , retention_rate<br>FROM result_data<br>unpivot<br>(<br>    retention_rate<br>    for retained_days in (d2, d3, d4, d5, d6, d7, d14, d30, d90, d183, d365)<br>) unpiv<br>;</pre><p>In Section2, these are the points to pay attention to:</p><ol><li>Although it requires some manual work, you can add or remove the day numbers in the analysis. The common practice are d2, d3, d7, d14, d30, d90, half year, or full year depending on your business requirements. This is also the reason why I mentioned that it might not be as flexible as Section1. Let’s say if your stakeholder ask you to display the day17 number, you will need to add a new line and re-run the queries and visuals. Luckily, it’s extremely rare.</li><li>The tables “data_per_day” and “result_data” in the Section 2 are the backbone data for the Stair-like Tabular Chart above. The unpivot process will help you to create the second and the third line charts shown above using “retained_days” as filter in your Viz design. You can try it with and without the unpivot process and implement into your Viz design and you will understand why it’s more convenient to add the unpivot process.</li><li>In the Complex Version-Section2, the more variables added, the more challenge to organize your Viz charts. Usually we compare 1 to 2 variables (3 in my example). More variables will become confusing and challenge to deliver information.</li></ol><p>I hope these will help you to analyze the Retention Rate easier. I recommend you use your own data to test and compare, and you will have a better understanding with your own use case.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=48f03dd19014" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[BigQuery: Query Performance Optimization BQ數據抓取優化]]></title>
            <link>https://medium.com/@williamwnsong/bigquery-query-performance-improvement-bq%E6%95%B8%E6%93%9A%E6%8A%93%E5%8F%96%E5%84%AA%E5%8C%96-53d391b811c?source=rss-4588b3533f10------2</link>
            <guid isPermaLink="false">https://medium.com/p/53d391b811c</guid>
            <category><![CDATA[performance-improvement]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[performance-management]]></category>
            <category><![CDATA[query-optimization]]></category>
            <dc:creator><![CDATA[William Song]]></dc:creator>
            <pubDate>Wed, 16 Aug 2023 08:21:44 GMT</pubDate>
            <atom:updated>2023-08-17T06:16:43.998Z</atom:updated>
            <content:encoded><![CDATA[<p>My apology, I forgot where I researched the methods to provide the reference link here. I believe there are many posts regarding the BigQuery performance optimization on the internet for you to search if you need more thorough explanations. Here, I just write down the key points in my note and would like to share with everyone, and I hope these would be beneficial for you too.</p><pre>### Performance Improvement ###<br><br># Syntax<br>-- 1. Use less SELECT * wildcard, use only the columns needed<br>-- 2. Denormalize schemas and take advantage of nested and repeated fields (counter intuitive, normalization isn&#39;t better in BQ)<br>-- 3. Use granular suffixes in table wildcards for more specificity<br>select col_1, col_2<br>from `datasetname.table_202307*`<br>where _table_suffix between &#39;01&#39; and &#39;10&#39;<br>-- is better than<br>select col_1, col_2<br>from `datasetname.table_*`<br>where _table_suffix between &#39;20230701&#39; and &#39;20230710&#39;<br><br># Data Connection<br>-- 1. Use BQ native storage for the best performance<br>-- 2. External direct data connections cannot be cached (slower)<br>-- 3. Do not read external data while live editing it, could create race conditions<br><br># Shuffling (query executing stage)<br>-- 1. Pre-filter data before doing JOINs<br>-- 2. Many shuffle stages can indicate data partition issues (skew) (usually see 1 worker bottle-necked)<br><br># WITH clause pitfall<br>-- 1. Frequently filtering and transforming the same results? Store them into a permanent table<br>-- 2. WITH clause queries are not materialized and are re-queried if referenced more than once<br>-- For example:<br>with t1 as (<br>  select active_date, userid, pay_amount, join_date, other_columns<br>  from users <br>  where active_date &gt;= &#39;2023-01-01&#39;<br>)<br>, t2 as (<br>  select active_date, userid, pay_amount, join_date, other_columns<br>  from users -- read from physical user table<br>  where active_date &gt;= &#39;2023-01-01&#39; <br>    and pay_amount &gt; 0<br>)<br>, t3 as (<br>  select active_date, userid, pay_amount, join_date, other_columns<br>  from users -- read from physical user table<br>  where active_date &gt;= &#39;2023-01-01&#39; <br>    and join_date &gt;= &#39;2022-01-01&#39;<br>)<br>select .....;<br>-- is BETTER than<br>with t1 as (<br>  select active_date, userid, pay_amount, join_date, other_columns<br>  from users <br>  where active_date &gt;= &#39;2023-01-01&#39;<br>)<br>, t2 as (<br>  select active_date, userid, pay_amount, join_date, other_columns <br>  from t1 -- read from t1 and trigger query run<br>  where pay_amount &gt; 0<br>)<br>, t3 as (<br>  select active_date, userid, pay_amount, join_date, other_columns<br>  from t1 -- read from t1 and trigger query run <br>  where join_date &gt;= &#39;2022-01-01&#39;<br>)<br>select .....;<br><br><br># GROUP BY on an ID with many distinct values (high-cardinality fields)<br>-- 1. If group by across many distinct values, more forced shuffles are performed (bad performance)<br><br># UDFs<br>-- 1. Javascript UDFs require BQ to launch a Java subprocess to run<br>-- 2. Use UDFs when BQ SQL isn&#39;t capable, transform UDFs to BQ SQL if capable<br>-- 3. Concurrent rate limits: for non-UDF queries: 50; for UDF queries: 6<br><br># Join Condition<br>-- 1. no accidental cross joins<br>-- 2. Use _TABLE_SUFFIX filter as granular as possible<br>-- 3. Use Window Functions instead of Self-Joins<br><br># Sort Last<br>-- 1. Large sorts should be the last opertion in queries<br>-- 2. If need to sort early, filter or limit data before sorting</pre><p>很抱歉我忘記當時的學習來源，無法提供原始連結給大家參考，當時寫下了對數據抓取有幫助的重點，在這邊分享給大家，希望對有需要的人能提供幫助。</p><pre>### BigQuery 數據抓取效率優化 ###<br><br># 腳本結構<br>-- 1. 減少使用 &quot;SELECT * &quot; 全選欄位的方式，盡量只抓取需要的欄位，這適用所有 SQL 引擎<br>-- 2. 盡量多使用 Nested and Repeated Fields 的數據結構，少使用 Normalize Schemas (違反傳統邏輯)<br>-- 傳統上我們受到的 SQL 訓練都是盡可能的做 Normalization，但 BigQuery 畢竟是大數據邏輯<br>-- 平攤的表格結構反而更有效率<br>-- 3. 指定表名在尾部使用 Wildcard 時，盡可能縮小維度，比如說有張表名叫 `dataset.table_20230701`，<br>-- 且尾部的日期設定會依每日寫入新內容而增加新表，我如果只需要搜尋7月的前10天內容，搜尋時寫 <br>select col_1, col_2<br>from `datasetname.table_202307*`<br>where _table_suffix between &#39;01&#39; and &#39;10&#39;<br>-- 會好過<br>select col_1, col_2<br>from `datasetname.table_*`<br>where _table_suffix between &#39;20230701&#39; and &#39;20230710&#39;<br><br># 數據連結<br>-- 1. Google 原始數據庫有更好的連結表現<br>-- 2. 外部連結無法被預先暫存，相對來說速度會較慢<br>-- 3. 外部連結的數據來源，在抓取時不要同時更新外部連結的數據內容，可能導致競爭狀態而卡住<br><br># 跑抓取數據的程式腳本時<br>-- 1. 先做條件篩選再做 JOIN<br>-- 2. Many shuffle stages can indicate data partition issues (skew) (usually see 1 worker bottle-necked)<br>-- 抱歉第二點我還沒遭遇過，但我查詢的意思似乎是如果出現太多 shuffle stage，代表 partition 的設定不太平均，<br>-- 有的 partition 規則太寬，有的 partition 規則太嚴導致數據內容大小差異過大<br>-- 歡迎大神糾正或補充<br><br># WITH 語法可能的陷阱，雖然很方便很好用，但仍注意以下)<br>-- 1. 如果重複篩選跟轉換相同的數據源，那乾脆把它存入一個永久表裡<br>-- 2. WITH 語法抓取的數據內容不會實存，在它下面的腳本重複引用的話，就會重複執行<br>-- 比如說以下的例子就不是好的示範，t2 跟 t3 都要讀 t1，導致 t1 被重複執行多次<br>-- 那 t1 表的執行結果需要頻繁的被引用分析，那也許可以先將結果存儲下來，減少一直被執行<br>-- 或避免一直讀 t1 跑 t1 的內容，t2 跟 t3 可以修改為直接讀  users 表(實體已儲存的表)，設定篩選條件<br>with t1 as (<br>  select * from users where active_date &gt;= &#39;2023-01-01&#39;<br>)<br>, t2 as (<br>  select * from t1 where pay_amount &gt; 0<br>)<br>, t3 as (<br>  select * from t1 where join_date &gt;= &#39;2022-01-01&#39;<br>)<br>select .....;<br><br><br># GROUP BY on an ID with many distinct values (high-cardinality fields)<br>-- 1. If group by across many distinct values, more forced shuffles are performed (bad performance)<br>-- 如果 group by 的參數內有太多不同的結果，會導致表現下降<br>-- 比如說某個分析要 group by 美國五大區 North West East South Central 跟 group by 美國 50 個州<br>-- 只 group by 區域肯定較快<br>-- 這其實必較看分析需求，只是放在心上，未來如果分析條件可以選擇的話，知道怎麼優化<br><br># UDFs = 自定義的 functions<br>-- 1. Javascript UDFs 需要 BQ 另外跑 Java 次行程 (subprocess)<br>-- 2. 盡量在 BQ SQL 語法無法執行的情況下，才使用 UDF<br>-- 假設有個任務是跑 BQ SQL 也行 UDF 也行，那就建議優先採用 BQ SQL<br>-- 我自己遇到的狀況是  JSON 拆解任務，可能會需要在 UDF 或 SQL 之間選擇，<br>-- 但關於 JSON 這又是另一個深澳的故事了，未來有機會分享<br>-- 3. Concurrent rate limits: for non-UDF queries: 50; for UDF queries: 6<br>-- 腳本同時執行上限，非 UDF 可同時執行 50 個，而 UDF 只有 6 個<br><br># 做 Join 時的條件設定<br>-- 1. 別輕易使用 CROSS JOIN，結果是倍數成長，很容易爆量<br>-- 比如我有張一千萬筆數據的表格(大數據的世界裡非常常見)，不小心 CROSS JOIN 另一張一百萬行的表.....執行起來跑的天長地久跑不完<br>-- 2. 呼應前面提到的 Wildcard 使用，如果Join 前抓取的表格跨多個表名設定，盡可能在 _TABLE_SUFFIX 篩選時就將條件設定的越細越好<br>-- 比如說 `dataset.table_2023080*` 好過於 `dataset.table_202308*`<br>-- 3. Use Window Functions instead of Self-Joins<br>-- 當 Window Function 與 self join 語法可以達到相同結果時，優先選擇 window function<br><br># Sort Last 排序在腳本的最後結果再做<br>-- 1. 排序的結果龐大，那就將排序設定放在程式腳本的最後面再做<br>-- 2. 如果在早期就需要排序處理，那也盡可能先縮小前置表格的擷取範圍再排序</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=53d391b811c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[BigQuery:Merge Tables 合併表格]]></title>
            <link>https://medium.com/@williamwnsong/bigquery-merge-tables-%E5%90%88%E4%BD%B5%E8%A1%A8%E6%A0%BC-6b595afa1b5f?source=rss-4588b3533f10------2</link>
            <guid isPermaLink="false">https://medium.com/p/6b595afa1b5f</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[cte]]></category>
            <category><![CDATA[upsert]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[merge]]></category>
            <dc:creator><![CDATA[William Song]]></dc:creator>
            <pubDate>Mon, 24 Jul 2023 06:40:15 GMT</pubDate>
            <atom:updated>2023-07-24T06:51:27.556Z</atom:updated>
            <content:encoded><![CDATA[<p>Reference 1 : <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement">https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement</a></p><p>Reference 2 : <a href="https://stackoverflow.com/questions/48177241/google-bq-how-to-upsert-existing-data-in-tables">https://stackoverflow.com/questions/48177241/google-bq-how-to-upsert-existing-data-in-tables</a></p><p>We sometimes encounter a situation that I have a main reporting table, which requires constant updates from another source table every day or from time to time. (INSERT is enough if your task is simple, but MERGE is a lot safer for NoSQL data warehouse to prevent potential duplicates.) Or you have two data tables with the same schema but contain some same and some different data rows, and you just want to combine the tables into one while keeping/removing duplicates. (My personal use cases were always removing duplicates.) When I first encountered this problem, the solution I googled was using “upsert” (Update and Insert) method. I never got familiar with this method as I found another method, which is using Merge statement. The concept isn’t hard and I am going to provide some examples that solved the tasks I encountered. For more use cases, please check the reference 1 link above.</p><p>From the examples, you can already see that the MERGE statement is very useful and covers many different situations to fit your task needs. The main driver is at the bottom when checking MATCH condition, you can setup a reaction. You can even have multiple MATCH conditions with different reactions.</p><pre>-- Basic Example<br>MERGE merge_example.table_data T -- as Target_Table<br>USING merge_example.table_changes S -- as Source_Table<br>ON T.id = S.id<br>WHEN MATCHED THEN<br>  UPDATE SET T.value = S.value<br>WHEN NOT MATCHED THEN<br>  INSERT (id, value) VALUES(id, value)<br>;<br><br>-- Complicate Example<br>MERGE `project.dataset.table` T -- as Target_Table<br>USING (<br>    WITH t1_example AS (<br>        SELECT *<br>        FROM(<br>            SELECT *<br>            FROM subqueries<br>        ) AS tb1<br>    )<br>    , t2_example AS (<br>        SELECT *<br>        FROM<br>        (<br>            SELECT *<br>            FROM subquery_a<br>        ) AS a<br>        LEFT JOIN<br>        (<br>            SELECT *<br>            FROM subquery_b<br>        ) AS b ON a.id= b.id<br>        GROUP BY x,y,z<br>    )<br>    , t3_example AS (<br>        SELECT *<br>        FROM t3<br>        LEFT JOIN join_example AS je<br>            ON t3.id = je.id<br>    )<br>    SELECT *<br>    FROM t1<br>    FULL JOIN (<br>        SELECT * <br>        FROM (<br>            SELECT *<br>            FROM t2<br>        ) AS t3<br>        ON t1.event_id = t3.event_id<br>) S -- as Source_Table<br>ON T.id1 = Source_Table.id1 AND T.id2 = S.id2 AND T.id3 = S.id3<br>WHEN NOT MATCHED BY SOURCE AND T.condition1 = some_condition_parameter AND T.utc_date = some_date_parameter THEN<br>    DELETE<br>WHEN MATCHED THEN<br>UPDATE SET T.variable1 = S.variable1<br>    , T.variable2 = S.variable2<br>    , T.variable3 = S.variable3<br>    ...<br>    ...<br>    ...<br>    , T.variableN = S.variableN<br>WHEN NOT MATCHED THEN<br>INSERT(variable1, variable2, variable3, ... , variableN)<br>VALUES(value1, value2, value3, ... , valueN)<br>;</pre><p>我們有時會遇到任務是要將兩張同樣結構但內容不完全相同的表格合併再一起，或是某個報表型的主表需要定期補充新的數據內容。以前搜尋解方時會找到 Upsert (Update + Insert) 的處理方式，但其實 Merge 是更輕鬆好寫的方法。從樣例裡可以看到 MERGE statement 底下可以是單純兩張表合併，也可以在 Source_Table 區塊裡寫入 Common Table Expression 語法抓取更複雜的數據源。</p><p>另外 MERGE 有很多不同的使用場景，也許你的合併表格是要同時保留所有數據，或是要移除重複，或是針對某些條件移除或某些條件保留或寫入，都有不同的對應方式。MERGE 強大的地方在於可以是很單純的比對處理，也可以設定多個不同的比對來結果安排不同的處理方式。 如果你的情況複雜，建議到 Reference 1 連結參考 BigQuery 官方樣例。</p><p>(小岔題: 針對主報表補充內容的任務，如果數據單純的情況下，也許 INSERT 就可完成任務，但在大數據NoSQL資料儲存方式越來越普及的情況下，很容易出現數據複製的情況導致錯誤。MERGE 可以避免此問題。)</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6b595afa1b5f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL: Find The Most Consecutive Days SQL計算最大連續天數]]></title>
            <link>https://medium.com/@williamwnsong/find-the-highest-consecutive-days-%E6%9C%80%E5%A4%9A%E9%80%A3%E7%BA%8C%E5%A4%A9%E6%95%B8-11c72661f074?source=rss-4588b3533f10------2</link>
            <guid isPermaLink="false">https://medium.com/p/11c72661f074</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[window-functions]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[consecutive]]></category>
            <dc:creator><![CDATA[William Song]]></dc:creator>
            <pubDate>Fri, 14 Jul 2023 05:06:17 GMT</pubDate>
            <atom:updated>2023-07-14T05:19:27.636Z</atom:updated>
            <content:encoded><![CDATA[<p>Reference: <a href="https://stackoverflow.com/questions/54225868/longest-consecutive-days-count-for-bigquery">https://stackoverflow.com/questions/54225868/longest-consecutive-days-count-for-bigquery</a></p><pre><br>-- CALCULATE CONSECUTIVE DAYS<br>with base as (<br>  select distinct userid, login_date<br>  from data_source_table<br>)<br>select userid<br>    , max(consecutivie_days) as max_days<br>from (<br>  select userid<br>    , grp<br>    , count(1) consecutivie_days -- counts the number of rows for each group under each userid<br>  from (<br>    select *<br>        , countif(gap_days &gt; 1) over(partition by userid order by login_date) as grp -- under each userid&#39;s login gap_days, assign a new group when meet non consecutive days<br>    from (<br>      select userid<br>        , login_date<br>        , lag(login_date) over(partition by userid order by login_date) as pre_login_date -- sanity check could omit<br>        , date_diff(login_date, lag(login_date) over(partition by userid order by login_date), day) as gap_days -- for each userid calculate potential gap days, when gap_days = 1 then it&#39;s consecutive<br>      from base<br>    )<br>  )<br>  group by userid, grp<br>)<br>group by userid<br>order by max_days desc<br>;</pre><p>The “base” common table expression serves the purpose assuming a user might log in multiple times in a day or if your data table might not be very clean. We use “distinct” to distinguish each user each day. You can omit this part if your table is already clean.</p><p>“date_diff(login_date, lag(login_date) over(partition by userid order by login_date), day)” counts number of days gap between current login date and previous login date. If it’s consecutive, the outcome of gap days should be 1, otherwise ≥ 2.</p><p>“countif(gap_days &gt; 1) over(partition by userid order by login_date)” assigns group numbers for each user if their consecutive dates break. Each consecutive group under each user will have their own counts. You can test this line on your own if you remove “partition by” or “order by” or both, see what would happen.</p><p>“count(1)” finds the consecutive days for each group under each user, then we can use max() function to find the max consecutive days for each user.</p><p>SQL計算最大連續天數，首先我建立一個 “base” common table expression 目的是假設原始數據表不是那麼乾淨，或是一個 user 可能在一天當中登入多次，我們使用 &quot;distinct&quot; 語法清理乾淨，確保一個 user 每個登入天只有一筆紀錄，但如果你知道你的表格已經很乾淨了可以跳過此步驟。</p><p>“date_diff(login_date, lag(login_date) over(partition by userid order by login_date), day)” 計算當行的日期與前一行的日期差距幾天，如果是連續天則結果會是1，若否則 ≥ 2.</p><p>“countif(gap_days &gt; 1) over(partition by userid order by login_date)” 在每個 user底下為每個連續天分配一個相同號碼，當有跳天發生時此號碼 +1，然後以此號碼標示分群統計。有興趣的話可以自行測試看看移除 partition by 或移除 order by 時會發生什麼情況。</p><p>“count(1)” 統計每個 user 底下的各個連續天群組連續天數，然後我們再用 max() function 去計算每個 user 最高的連續天數是多少。</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=11c72661f074" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL: Find Duplicates SQL找出重複數據]]></title>
            <link>https://medium.com/@williamwnsong/sql-find-duplicates-%E6%89%BE%E5%87%BA%E9%87%8D%E8%A4%87%E6%95%B8%E6%93%9A-935dd7608e63?source=rss-4588b3533f10------2</link>
            <guid isPermaLink="false">https://medium.com/p/935dd7608e63</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[group-by]]></category>
            <category><![CDATA[find-duplicates]]></category>
            <dc:creator><![CDATA[William Song]]></dc:creator>
            <pubDate>Thu, 13 Jul 2023 01:47:46 GMT</pubDate>
            <atom:updated>2023-07-14T05:23:30.464Z</atom:updated>
            <content:encoded><![CDATA[<p><a href="https://www.sqlservertutorial.net/sql-server-basics/sql-server-find-duplicates/">https://www.sqlservertutorial.net/sql-server-basics/sql-server-find-duplicates/</a></p><pre>-- Check Duplicates<br>SELECT *<br>FROM user_data<br>WHERE user_id IN (<br> SELECT user_id<br> FROM user_data<br> GROUP BY user_id<br> HAVING COUNT(user_id)&gt;1<br>);</pre><ol><li>Understand the key column you want to identify duplicate.</li><li>Using Group By key and Having COUNT(key) &gt; 1 to track those rows</li></ol><p>找出重複數據</p><ol><li>先確認以哪個參數欄位為搜尋定義的 key</li><li>使用 GROUP BY key 及 HAVING COUNT(key) &gt; 1 即可找出對應的條列</li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=935dd7608e63" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[The Beginning….. Hello World!]]></title>
            <link>https://medium.com/@williamwnsong/the-beginning-hello-world-925c0673c045?source=rss-4588b3533f10------2</link>
            <guid isPermaLink="false">https://medium.com/p/925c0673c045</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[analytics]]></category>
            <dc:creator><![CDATA[William Song]]></dc:creator>
            <pubDate>Thu, 13 Jul 2023 01:24:00 GMT</pubDate>
            <atom:updated>2023-07-14T04:34:12.064Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*1orG6S_ggrT_iC92.jpg" /><figcaption><a href="https://www.investopedia.com/terms/d/data-analytics.asp">https://www.investopedia.com/terms/d/data-analytics.asp</a></figcaption></figure><p>After few years of work experience in Data Analytics, I want to log and share some techniques (mainly SQL) I encountered and learned from work. They will not be tutorial posts but just important elements for my own references and in hope to help those who encounter similar challenges as well. On the top of every post, I will also add links where I learned those techniques from as I am merely standing on the shoulders of giants. Feel free to go to those links if you want to know the concepts and knowledge thoroughly. In addition, most of my future posts will be bilingual, English and Traditional Chinese. Please feel free to leave any comment if you have any idea to share or to correct mistakes I made.</p><p>在數據領域工作幾年後，發現有很多在學校沒學到但在職場上經歷工作學習到的一些技巧，想寫下來留做個人紀錄及分享給網上為某些任務嘗試搜尋方法的人，希望這些貼文能對大家有幫助。另外這些分享文章並非是以教學為出發點，僅僅只是核心概念，但我會附上我參考的其他大神的貼文連結或語法的官方連結，有興趣更理解概念的朋友可以點進去參考看看。每篇貼文會以英文及中文呈現，也歡迎留下你的想法或糾正我的錯誤。</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=925c0673c045" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>