<?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 Dakota Smith on Medium]]></title>
        <description><![CDATA[Stories by Dakota Smith on Medium]]></description>
        <link>https://medium.com/@dakotasmithdata?source=rss-dcadf2cebe12------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*YXmQtnv7D8veX9K9oLBBUg.png</url>
            <title>Stories by Dakota Smith on Medium</title>
            <link>https://medium.com/@dakotasmithdata?source=rss-dcadf2cebe12------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Mon, 16 Nov 2026 21:58:54 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@dakotasmithdata/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[Hacking MySQL’s JSON_ARRAYAGG Function to Create Dynamic, Multi-Value Dimensions]]></title>
            <link>https://medium.com/data-science/hacking-mysqls-json-arrayagg-function-to-create-dynamic-multi-value-dimensions-211074115d28?source=rss-dcadf2cebe12------2</link>
            <guid isPermaLink="false">https://medium.com/p/211074115d28</guid>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[data-aggregation]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[json]]></category>
            <dc:creator><![CDATA[Dakota Smith]]></dc:creator>
            <pubDate>Tue, 25 Jul 2023 06:12:04 GMT</pubDate>
            <atom:updated>2023-07-25T15:58:49.105Z</atom:updated>
            <content:encoded><![CDATA[<h4>Making up for one of MySQL’s lesser-known shortcomings</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*wnp1dLpUt4g_9aDh" /><figcaption>Photo by <a href="https://unsplash.com/fr/@esen_aza?utm_source=medium&amp;utm_medium=referral">Azamat E</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a>. Thanks, Azamat!</figcaption></figure><h3>Introduction</h3><p>Let’s imagine that we’re members on the Data team at a subscription box company. In a MySQL database, transaction records of purchases are written to table named subscriptions. Metadata excluded, the table contains a customer_id and subscription field, and looks something like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1002/1*lxOh4C8QLPM4xnG9_PX0pA.png" /><figcaption>The subscriptions table. (Note: All images, unless otherwise noted, are by the author)</figcaption></figure><p><em>Note that in this example scenario, a customer can have multiple subscriptions. The uniqueness of each record is in defined by both the customer ID and a subscription, i.e., no customer can have the same subscription twice. If you want to load this test data into your own database, you can find the code to do so </em><a href="https://github.com/dakotaleesmith/pyProjects/blob/main/tutorials/mysql_json_arrayagg_tutorial/create_table.sql"><em>here</em></a><em>.</em></p><p>As a subscription box company, our goal is to sell more subscription boxes. To that end, the Product team recently pointed out that all of our current customers have more than one subscription. They’re curious what this says about customer behavior. They ask our team for a data model showing combinations of subscriptions purchased by user, as well as which combinations are most common.</p><p>The Marketing team has also showed interested in this model. They believe the results might be useful for marketing bundled product promotions, profiling customers, and targeting email campaigns. For these same reasons, they’re also asking to see the most common total <em>number</em> of subscriptions purchased by each customer.</p><p>In short, the requested data model hopes to answer some important questions that’ll ideally end in higher subscription box sales. The question is, how exactly should we execute it?</p><p>In this article, we’re going to tackle a unique data modeling challenge by making up for one of MySQL’s lesser-known shortcomings. We’re going to go over qualitative aggregation, the JSON data type, and how to force MySQL to order a list of values in a way that produces distinct, multi-value dimensions.</p><h4>Table of Contents</h4><ul><li><a href="#0c85">Aggregations as Dimensions</a></li><li><a href="#3a1b">A brief overview of the JSON datatype in MySQL</a></li><li><a href="#5b60">JSON_ARRAYAGG</a></li><li><a href="#0793">Using ROW_NUMBER to force the ordering of values</a></li><li><a href="#c0e9">To recap</a></li></ul><h3>Aggregations as Dimensions</h3><p>Conceptually, what we need to do is relatively simple: We need to bundle (group) our subscriptions by customer. Then we need to take a look at these bundles and see which are the most common and how many subscriptions are in them.</p><p>In data modeling terms, we’re looking at some form of aggregation: specifically, aggregating subscriptions by customer.</p><p>It’s normal to think of aggregate functions in the quantitative sense (SUM, COUNT, etc.), and this is largely because this is what most aggregate functions do in SQL. But we can also aggregate concatenated string values into longer, list-like strings.</p><p>The challenge with this though is lies in accessing, manipulating, or otherwise evaluating the values within these concatenated strings. MySQL is going to treat the value foo, bar, hello, world not as a list, but as text.</p><p>Why is this relevant? Primarily because in our hypothetical scenario, we want to count the number of subscriptions in each combination. We don’t want a long comma-delimited string, we want something more truly list-like.</p><p>Solving this in Python would be simple—using pandas, maybe polars, or even just Python-native data structures. But there are many cases where this isn’t an option. Maybe the Data team is a strictly dbt-only shop; or, perhaps more commonplace, you work at a company where the IT department has local environments severely locked down.</p><p>Whatever the case, if you have only SQL at your disposal, you need a solution that will provide you the most readable code and the most flexible results. Achieving this is not intuitive. For example, my first instinct when encountering this problem was to use GROUP_CONCAT, the function that concatenates strings according to your defined grouping:</p><pre>WITH<br>  subscriptions_grouped AS (<br>  SELECT<br>    customer_id,<br>    GROUP_CONCAT(subscription) AS subscriptions<br>  FROM <br>    subscriptions<br>  GROUP BY customer_id<br>  )<br>SELECT<br>  subscriptions,<br>  COUNT(*) AS num_accounts<br>FROM subscriptions_grouped<br>GROUP BY subscriptions<br>;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/508/1*k7kA4iJ7jzXIoRu2OmFCbw.png" /><figcaption>Query results</figcaption></figure><p>As you can see, the aggregation <em>worked</em>, technically, but it didn’t work along the lines of our business logic. See the first and last rows. The subscriptions combination of “international_snacks, self_care” is the same combination as “self_care, international_snacks”. (The same is true of the 2nd and 4th rows.)</p><p>We can use an ORDER BY clause within GROUP_CONCAT to solve this particular issue:</p><pre>WITH<br>  subscriptions_grouped AS (<br>  SELECT<br>    customer_id,<br>    GROUP_CONCAT(subscription ORDER BY subscription) AS subscriptions<br>  FROM <br>    subscriptions<br>  GROUP BY 1<br>  )<br>SELECT<br>  subscriptions,<br>  COUNT(*) AS num_accounts<br>FROM subscriptions_grouped<br>GROUP BY subscriptions<br>;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/508/1*KRwmeht5Zw7DReKyXGSCTA.png" /><figcaption>Query results</figcaption></figure><p>But this still leaves up with the problem of counting how many subscriptions are in each combination.</p><p>There is a way to do it. But not only is it needlessly complex and not very readable, in my opinion, but it also comes with some not-so-obvious pitfalls.</p><p>A quick search on how to count the number of values in a comma-delimited string in MySQL turns up a <a href="https://stackoverflow.com/a/7020024/16018746">solution</a> from StackOverflow that, translated for our purposes, amounts to this (subscriptions_grouped CTE excluded):</p><pre>SELECT<br>  subscriptions,<br>  LENGTH(subscriptions) - LENGTH(REPLACE(subscriptions, &#39;,&#39;, &#39;&#39;)) + 1 AS num_subscriptions,<br>  COUNT(*) AS num_accounts<br>FROM subscriptions_grouped<br>GROUP BY subscriptions<br>;</pre><p>Which is essentially counting the number of commas and then adding 1 to the result. This works. But not only is this answer difficult to understand at a glance, it also introduces a potential mistake: the LENGTH and CHAR_LENGTH function <a href="https://stackoverflow.com/a/1734340/16018746">don’t count the same thing</a>.</p><p>As you can probably guess, this article details obstacles I ran into at work when I found myself in a similar situation.</p><p>Ultimately, the solution was in a somewhat hack-y, but remarkably comprehensible, workaround using the native MySQL JSON data type.</p><h3>A brief overview of the JSON data type in MySQL</h3><p>The JSON data type in MySQL was added in version 5.7.8, and provides a lot of useful utility for both storage and modeling.</p><p>Under the JSON data type umbrella (officially called “JSON documents”) are two different data structures: JSON arrays and JSON objects.</p><p>A JSON array can simply be thought of as an array (a list, if you’re a Pythonista): values enclosed by square brackets [ ] and separated by commas.</p><ul><li>An example MySQL JSON array value: [“foo”, “bar”, 1, 2]</li></ul><p>A JSON object can be thought of as a hash table (or, again in Python terms, a dictionary): key-value pairs, separated by commas, and enclosed by curly brackets { }.</p><ul><li>An example of a MySQL JSON object value: {“foo”: “bar”, 1: 2}</li></ul><p><a href="https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html">MySQL has a number of functions that can be used to deal with both of these formats</a>—almost none of which perform any sort of aggregation.</p><p>Thankfully, though, there are two that do. And they both return JSON documents, which means we can use MySQL’s built-in functions to access the values therein.</p><h3>JSON_ARRAYAGG</h3><p>The MySQL function JSON_ARRAYAGG acts a lot like GROUP_CONCAT. The biggest difference is that it returns a JSON array, which, again, comes with several helpful built-in functions linked above.</p><p>The JSON array data type solves one of our two problems with astounding simplicity: the problem of reliably counting the number of subscriptions in a combination. This is accomplished using the <a href="https://dev.mysql.com/doc/refman/5.7/en/json-attribute-functions.html#function_json-length">JSON_LENGTH</a> function. The syntax is wonderfully straightforward:</p><pre>SELECT JSON_LENGTH(JSON_ARRAY(&quot;foo&quot;, &quot;bar&quot;, &quot;hello&quot;, &quot;world&quot;));<br>-- JSON_ARRAY function used here just to quickly create an example array</pre><p>The result of this statement is 4, since there are 4 values in the generated JSON array.</p><p>But let’s return to the combination of subscriptions. Unfortunately, JSON_ARRAYAGG doesn’t come with the ordering functionality that GROUP_CONCAT has. Ordering the subscription values, even in a CTE before the base query, doesn’t return the desired results:</p><pre>WITH<br>  subscriptions_ordered AS (<br>  SELECT<br>    customer_id,<br>    subscription<br>  FROM subscriptions<br>  ORDER BY subscription <br>  )<br>  , subscriptions_grouped AS (<br>  SELECT<br>    customer_id,<br>    JSON_ARRAYAGG(subscription) AS subscriptions,<br>    JSON_LENGTH(JSON_ARRAYAGG(subscription)) AS num_subscriptions<br>  FROM <br>    subscriptions_ordered<br>  GROUP BY customer_id<br>  )<br>SELECT<br>  subscriptions,<br>  COUNT(*) AS num_accounts<br>  num_subscriptions<br>FROM subscriptions_grouped<br>GROUP BY subscriptions<br>;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/753/1*ckchIMzZ75Afw7DR3FvfKA.png" /><figcaption>Query results</figcaption></figure><p>The number of subscriptions in each combination is there, thanks to the JSON_LENGTH function—but combinations that are effectively the same are once again mischaracterized as distinct because of their order.</p><h4>Using ROW_NUMBER to force the ordering of values</h4><p>ROW_NUMBER is a window function that creates an index. The index has to be defined; that is, you have to tell it where to start, how to increment (directionally), and where to end.</p><p>We can see a quick example of this by applying the ROW_NUMBER function and telling it to order by the subscription field:</p><pre>SELECT <br>  customer_id, <br>  subscription, <br>  ROW_NUMBER() OVER(ORDER BY subscription) AS alphabetical_row_num<br>FROM subscriptions<br>;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/484/1*bOkj9KeZ9H4D7ojsfTn-Tg.png" /><figcaption>Query results</figcaption></figure><p>Look closely at the results. <strong><em>Even though we didn’t use an </em></strong>ORDER BY<strong><em> statement at the end of our query, the data is nonetheless ordered according to the </em></strong>ORDER BY<strong><em> in the </em></strong>OVER<strong><em> clause.</em></strong></p><p>But of course this still isn’t exactly what we want. What we need to do next is add a PARTITION BY clause to our window function, so that the ordering of the results are related to (and in fact bounded by) each customer ID. Like so:</p><pre>SELECT <br>  customer_id, <br>  subscription, <br>  ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY subscription) AS alphabetical_order<br>FROM subscriptions<br>;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/461/1*xp91jBD88sKxajQtPozh6w.png" /><figcaption>Query results</figcaption></figure><p>You can probably see where this is going.</p><p>If we execute the JSON_ARRAYAGG function against these results in a CTE, we see that the duplicate combinations now look exactly the same, thanks to the subscriptions being forced into an alphabetical order by the ROW_NUMBER function:</p><pre>WITH <br>  subscriptions_ordered AS (<br>  SELECT <br>    customer_id, <br>    subscription, <br>    ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY subscription) AS alphabetical_order<br>  FROM subscriptions<br>  )<br>SELECT<br>  customer_id,<br>  JSON_ARRAYAGG(subscription) AS subscriptions<br>FROM subscriptions_ordered<br>GROUP BY 1<br>ORDER BY 2<br>;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/560/1*l1nTK_kxEiEmeg_XewEMog.png" /><figcaption>Query results</figcaption></figure><p>Now all we need to do is add in the grouping CTE following the one executing ROW_NUMBER, and alter the base query:</p><pre>WITH <br>  subscriptions_ordered AS (<br>  SELECT <br>    customer_id, <br>    subscription, <br>    ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY subscription) AS alphabetical_order<br>  FROM subscriptions<br>  )<br>  , subscriptions_grouped AS (<br>  SELECT<br>    customer_id,<br>    JSON_ARRAYAGG(subscription) AS subscriptions,<br>    JSON_LENGTH(JSON_ARRAYAGG(subscription)) AS num_subscriptions<br>  FROM subscriptions_ordered<br>  GROUP BY customer_id <br>  )<br>SELECT<br>  subscriptions,<br>  COUNT(*) AS num_customers,<br>  num_subscriptions<br>FROM subscriptions_grouped<br>GROUP BY subscriptions<br>ORDER BY num_customers DESC<br>;</pre><p>This gives not only accurately distinct combinations of subscriptions, but also the number of customers who’ve purchased those combinations, and how many subscriptions comprise each of them:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/761/1*mNz0k3b0sA0sEv9ZzSo8XQ.png" /><figcaption>Query results</figcaption></figure><p>Voila!</p><h3>To recap</h3><ul><li>We wanted to know how many customers purchased different combination of subscriptions, and how many subscriptions were in each of those combinations. This presented two problems: how best to obtain the latter, and how to generate accurately distinct subscription combinations.</li><li>To obtain the number of subscriptions in each combination, we chose to go with one of MySQL’s JSON functions, JSON_ARRAYAGG. The resulting aggregation was returned to us as a JSON data type, allowing us to use the JSON_LENGTH function.</li><li>We then needed to force the ordering of values inside the JSON array so that duplicate combinations didn’t mistakenly appear distinct. To do this, we used the window function ROW_NUMBER in a CTE prior to the base query, partitioning by customer ID and ordering the subscriptions alphabetically (in ascending order).</li><li>This ultimately allowed us to aggregate up to accurately distinct combinations of subscriptions; and with this we were able to use a simple COUNT function to see how many customers had purchased each combination.</li></ul><h4>Thanks for reading! 🤓</h4><p><em>I hope this was helpful! If you know of any other clever tricks/workarounds in SQL (regardless of dialect), I’d love to hear about them. SQL has long been the de facto lingua franca for transforming structured data, but it’s not perfect. I always enjoy learning about innovative and/or clever solutions to real-world challenges. 🔥</em></p><p><em>I write semi-regularly about data engineering and analytics topics—the goal always being to write as clearly and simply as possible. If anything in this article confused you, please let me know in the comments. And if you’re interested in reading more articles like this, feel free to </em><a href="https://medium.com/@dakotasmithdata"><em>follow me</em></a><em> and/or </em><a href="https://www.linkedin.com/in/dakotaleesmith/"><em>connect on LinkedIn</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=211074115d28" width="1" height="1" alt=""><hr><p><a href="https://medium.com/data-science/hacking-mysqls-json-arrayagg-function-to-create-dynamic-multi-value-dimensions-211074115d28">Hacking MySQL’s JSON_ARRAYAGG Function to Create Dynamic, Multi-Value Dimensions</a> was originally published in <a href="https://medium.com/data-science">TDS Archive</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Using Python and the Terminal to Simplify Repetitive Data Tasks]]></title>
            <link>https://levelup.gitconnected.com/using-python-and-the-terminal-to-simplify-repetitive-data-tasks-27ecc419b063?source=rss-dcadf2cebe12------2</link>
            <guid isPermaLink="false">https://medium.com/p/27ecc419b063</guid>
            <category><![CDATA[zsh]]></category>
            <category><![CDATA[automation]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[terminal]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Dakota Smith]]></dc:creator>
            <pubDate>Wed, 08 Mar 2023 03:44:05 GMT</pubDate>
            <atom:updated>2023-03-09T02:30:03.782Z</atom:updated>
            <content:encoded><![CDATA[<p>A beginner-friendly guide to getting started with the macOS Terminal to call Python scripts in a few short characters</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*knMAqMZD6Vbc1wtS" /><figcaption>Photo by <a href="https://unsplash.com/@sickhews?utm_source=medium&amp;utm_medium=referral">Wes Hicks</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a>. Thanks, Wes!</figcaption></figure><h4><strong>Table of Contents</strong></h4><ul><li><a href="#1173">What we’ll be going over</a></li><li><a href="#96a7">A brief overview of the macOS Terminal and zsh</a></li><li><a href="#c0fd">Using Python to retrieve data</a></li><li><a href="#274d">How to shorten calling the Python script in your Terminal</a></li></ul><h3>Introduction</h3><p>A lot of the way I learn comes down to having the thought, “I bet there’s an easier way to do this.” Similar thoughts include: “I’m sick of writing this boilerplate code over and over,” and, “The twenty minutes a month this automation will save me will greatly enrich my life, surely.”</p><p>This impulse comes from a good place. Or at least a logically consistent one if you also enjoy coding. Quality code is documented, standardized, and reproducible. Automating simple tasks may not always be the most efficient use of time in the short term, but it embeds these characteristics into the workflow, which is useful in the long run.</p><p>For example, I had a SQL query at work I ran often, with minor adjustments here and there, against a database. Included in the WHERE clause was always a LOWER(&lt;field&gt;) LIKE “%&lt;value&gt;%” condition that for whatever reason irked me to write all the time. So I developed a Python script that could generate the query with whatever conditions were needed based on inputs from the Terminal, then wrote a zsh function to shorten the call to that script to about ten characters.</p><p>Was this necessary? No, I guess not, but it did improve my understanding of zsh and the sys Python library. Does it save me some time to this day? Yes. Does that saved time enrich my life?</p><p>Yes. You know what—yes, it does.</p><p>And it’s not difficult to replicate for other purposes. I’m going to show you how to do that exactly that here.</p><h4>What we’ll be going over</h4><ul><li>A brief overview of zsh</li><li>How to use Python to retrieve a value from a local JSON file</li><li>Using the sys module from the Python standard library to accept inputs from your Terminal</li><li>How to write a simple zsh function that can be typed into your Terminal to execute a Python script in far fewer characters than python3 /Users/dakotasmith/pyProjects/path_I_will_rarely_remember.py &lt;arg&gt;</li></ul><h4>What you need to know</h4><ul><li>A beginner’s knowledge of Python</li><li>How to open the Terminal</li></ul><h3>zsh</h3><h4>What is zsh?</h4><p>Pronounced “z-shell,” zsh is a command interpreter. Specifically, it’s an extended version of Bash. You can think of it as the language you use on a Mac to talk to your Terminal (if you’re using the current macOS, Catalina or a more recent release). But for our purposes right now we’re just going to focus on the .zshrc file.</p><p>The .zshrc file is a list of commands that are run, defined, or both, any time you open a new Terminal. It can define custom functions like the one we’ll eventually use to call our Python script. However, the .zshrc file doesn’t exist by default, so if you haven’t already done so, it has to be created.</p><p>You want the .zshrc file to be in your root directory, which is where a new Terminal will be pointed at in most cases. In a Terminal shell, type (or copy and paste) nano ~/.zshrc and hit Enter.</p><p>The resulting interface might not look familiar to inexperienced Terminal users, but it’s a text editor that comes with some handy keyboard shortcuts. We’re not going to do anything in this file right now except create it. Hit the following keys in the order described. I’ve added brief descriptions of what each keyboard shortcut does.</p><h4>nano keyboard shortcuts:</h4><ul><li><strong>Ctrl-O</strong>: save</li><li><strong>Enter</strong> (following Ctrl-O): confirm save</li><li><strong>Ctrl-X</strong>: exit nano</li></ul><p>We’ll come back to this .zshrc file in a bit. Let’s move on to our Python script.</p><h3>What’re we doing with Python again?</h3><p>We’re going to make an otherwise monotonous query to a local JSON file quick and easy. Again, this is just instructional; you can configure the script to whatever need you have.</p><p>For this tutorial, my particular JSON file — which you can find <a href="https://github.com/dakotaleesmith/pyProjects/blob/main/zsh_python_tutorial/sample_data.json">here</a> or generate yourself with <a href="https://github.com/dakotaleesmith/pyProjects/blob/main/zsh_python_tutorial/insert_data.py">this Python script</a> — contains some very important colors and their very important hex codes.</p><p>If you want to follow along with the example, I suggest creating a local project folder. If you want to get fancy, I also suggest creating a virtual environment for the project. (Nothing’s fancier than not having to manage dependency issues.)</p><p>However, as the only libraries used in this tutorial are from the Python standard library, you shouldn’t have any dependency issues unless you’re running Python 2.</p><p><em>Using and managing virtual environments is considered best practice when working on Python projects. For more info, start </em><a href="https://realpython.com/python-virtual-environments-a-primer/"><em>here</em></a><em>. I use </em><a href="https://realpython.com/intro-to-pyenv/"><em>pyenv</em></a><em> to manage mine. I’ll note that while I might often provide links to Real Python articles, I’m in no way personally affiliated with them; I just think they make fantastic content.</em></p><p>Okay. We have our project folder, we have our virtual environment (or not, woops), and we’ve created our .zshrc file. We’ve done a lot and have almost nothing to show for it. Cool, I guess!</p><p>Let’s write some code.</p><h4>The Almighty Code</h4><p>You can view the code for our Python script <a href="https://github.com/dakotaleesmith/pyProjects/blob/main/zsh_python_tutorial/get_color_value.py">here</a> (which, okay—it’s not that almighty). Feel free to skip this section if you’re not interested in how the code works.</p><p>In a .py file, we’ll start by importing our modules.</p><pre>import json<br>from sys import argv<br>from insert_data import file_path</pre><p><em>Note that the module insert_data is actually another .py file in the same directory (i.e., </em><a href="https://github.com/dakotaleesmith/pyProjects/tree/main/zsh_python_tutorial"><em>the project folder</em></a><em>). You don’t need to import this if you don’t want to. You could just as easily assign the variable file_path within the script. I don’t like repeating myself, so since I generated the file in insert_data.py, I’ve gone ahead and imported its respective file path.</em></p><p>Our first module is the json module, which will allow us to read from the JSON file that contains our data.</p><p>We then import argv from the sys module to access arguments passed from the Terminal.</p><p>And finally, from our project folder, we import the file_path variable defined in our insert_data.py file. (Again: just a design choice.)</p><p>Next we’ll define a simple function that reads our JSON and returns the data therein:</p><pre>def get_data() -&gt; json:<br>    with open(file_path, &quot;r&quot;) as f:<br>        return json.load(f)</pre><p><em>Another best practice: using the </em><em>with statement to make sure that after reading the JSON file, Python closes it no matter what happens (i.e., if an error occurs). The wrong loop with an unattended </em><em>open() function can crash your computer.</em></p><p>After this, we can write the function that will serve as our main program. I’ll take it step by step, and then include the function’s code in its entirety at the end.</p><p>First:</p><pre>def get_color_value(color: str) -&gt; dict:<br>    data = get_data()</pre><p>We start by defining a function get_color_value that accepts the parameter color. Type hints indicate that color should be a string and that the value returned by the function will be a dictionary. We then call the previously defined get_data() and assign its result to the variable data.</p><pre>assert color in [<br>    item.get(&quot;color&quot;) for item in data<br>], &quot;Color selected not available. Try red!&quot;</pre><p>Here, we assert that the argument passed into the parameter color is one that we actually have data on.</p><p>In layman’s terms: Sorry, but “cerulean” isn’t in our modest dict of colors. Er, I mean—in our dictionary of very important colors and their very important hex codes, cerulean didn’t make the cut. Sincerest apologies.</p><p>The assert statement acts as a checkpoint determining whether or not something is true. For example, if we’d written assert 1 == 1, nothing would happen, since 1 == 1 is true. However, if were to assert something that isn’t true, like assert 1 == 0, the application would throw an error. Accompanying that error would be whatever string is passed following the assertion.</p><p>Without our assert statement, if we entered a color that isn’t in the data, nothing would be returned by the function. This could seem like a bug, so instead we’re just going to head off at the pass all colors missing from our data, and in the error message, suggest something simpler — in this case, red.</p><pre>color_dict = {<br>    item[&quot;color&quot;]: item[&quot;value&quot;] for item in data if item[&quot;color&quot;] == color<br>}<br>return color_dict</pre><p>Here we create our final product. We do this by using a comprehension — a for loop, essentially, enclosed by the syntax of our chosen iterable (in this case, a dictionary, as indicated by the curly braces). Though you could just as easily write a for loop if that’s more comfortable.</p><p>And then we return color_dict, the extraordinarily important color and its extraordinarily important hex code.</p><p>Finally, our get_color_value function in all its glory:</p><pre>def get_color_value(color: str) -&gt; dict:<br>    data = get_data()<br>    assert color in [<br>        item.get(&quot;color&quot;) for item in data<br>    ], &quot;Color selected not available. Try red!&quot;<br>    color_dict = {<br>        item[&quot;color&quot;]: item[&quot;value&quot;] for item in data if item[&quot;color&quot;] == color<br>    }<br>    return color_dict</pre><p>Looks good! I’ll probably frame it.</p><p>Lastly, because right now our script doesn’t actually do anything, we need to call our main function inside an if __name__ == “__main__”: statement.</p><pre>if __name__ == &quot;__main__&quot;:<br>    color = str(argv[1])<br>    value = get_color_value(color)<br>    print(value)</pre><h4>Wait—if who equals equals what?</h4><p>A quick primer on if __name__ == “__main__” , because it can be a tricky concept if you’re new to Python.</p><p>Basically, it tells Python: If this script is being executed, do the following things.</p><p>So for one, it gives you an opportunity to set up what you want the script to do, and to make it readable (ideally). But more importantly, it protects you from accidentally executing the script in situations where you’re not trying to—e.g., when you’re importing it as a module in another script.</p><p>If you want to spend some time wrapping your head around this more, allow me to provide some <a href="https://realpython.com/if-name-main-python/#:~:text=Nesting%20code%20under%20if%20__,defined%2C%20but%20no%20code%20executes.">additional reading material</a>.</p><h4>Breaking it down:</h4><pre>if __name__ == &quot;__main__&quot;:<br>    color = argv[1]</pre><p>When we eventually input into the Terminal what color we’re looking for, argv[1] will read that value and pass it along to the color variable.</p><p><em>Spoiler alert: I’m gonna put red.</em></p><pre>value = get_color_value(color)<br>print(value)</pre><p>Then we call our get_color_value function, and pass in the input variable color.</p><p>This returns the color_dict dictionary into our new variable value, which then gets printed into the Terminal.</p><p>If you open your Terminal and navigate to your project folder (hint: use the cd function, which stands for <em>change directory</em>), you can see the script in action. Call it by entering python3 &lt;file_path&gt; &lt;color&gt;, substituting the variables for your own file path and color.</p><p>Mine looks like this: python3 zsh_python_tutorial/get_color_value.py red</p><h3>Okay, but that was a lot to type out</h3><p>Exactly. There simply isn’t enough time in the day to spend an extra minute frivolously hunting down a file path, relative or not.</p><p>It may seem silly given what the code does, but remember, this is a simple example. Entire workflows can be condensed to a single word using these concepts. At your pithy command, whole cities could rise and fall from the Terminal.</p><p>Maybe. Hopefully not. For now, let’s just retrieve some incredibly valuable color data.</p><p>In your Terminal, enter nano .zshrc. It’s time to create our zsh function.</p><p>Here’s what it looks like if you just want to copy and paste it into your nano editor:</p><pre>get_color() {<br>        cd /Users/dakotaleesmith/pyProjects<br>        python3 zsh_python_tutorial/get_color_value.py &quot;$1&quot;<br>        cd ~<br>}</pre><p>Obviously don’t cd to my local pyProjects directory. It won’t work unless your name happens to also be Dakota Lee Smith and you’ve followed along with the tutorial a little too closely. Insert your own paths where applicable (i.e., after the first cd and then after python3).</p><p>Now—with brevity as our guiding principle—let’s go over each line in this function.</p><pre>get_color() {</pre><p>Here we name the function and declare it as such. The opening curly braces tell zsh that the commands that follow are to be executed as a result of calling this function.</p><pre>cd /Users/dakotaleesmith/pyProjects</pre><p>The first command. All we’re doing here is pointing the Terminal at our local project folder. The function executes this command first before moving onto the next:</p><pre>python3 zsh_python_tutorial/get_color_value.py &quot;$1&quot;</pre><p>This we’ve seen already. It’s a call to our Python script.</p><p>The part to note here is &quot;$1&quot;. This is our color variable—it stands in for whatever color is passed to the zsh function as an argument.</p><p>This value is what’s returned by argv[1] in the Python script.</p><p>And finally:</p><pre>  cd ~<br>}</pre><p>Which, along with closing the get_color function using the curly brace at the bottom, simply points our Terminal back at our root directory. You don’t have to include this, but to me it’s always seemed polite. To my future self, that is. Because if you open that same Terminal shell later to do something else, you might not notice that it’s pointed at a directory other than your root. (This has been a common and mildly frustrating experience for me.)</p><p>Now that your function is inside your .zshrc file, you can call it from any future Terminal shell. But first, don’t forget to save. As a reminder, here’s how:</p><h4>nano keyboard shortcuts:</h4><ul><li><strong>Ctrl-O</strong>: save</li><li><strong>Enter</strong> (following Ctrl-O): confirm save</li><li><strong>Ctrl-X</strong>: exit nano</li></ul><p>After this you should be back in your regular ol’ Terminal. But wait! Don’t call the function yet.</p><p>We need to close that Terminal shell and open a new one. Our current shell hasn’t ran the updated .zshrc file. It has no idea what the get_color function does, and will laugh at you, probably, if you attempt to call it.</p><p>Once you’ve opened a new Terminal, you‘ll be able to call the function and retrieve whatever data you’ve assigned the Python script to retrieve.</p><h3>Conclusion</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*5RX37UfH0BivjoSR" /><figcaption>Photo by <a href="https://unsplash.com/pt-br/@nublson?utm_source=medium&amp;utm_medium=referral">Nubelson Fernandes</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>Wow! Would you look at that. Not only can I now obtain the hex code for a color with only 15 characters, but I’ve also fundamentally changed as a person:</p><pre>get_color green<br>&gt;&gt;&gt; {&#39;green&#39;: &#39;#0f0&#39;}</pre><p>I’ve moved on from red. I’m a green guy, now.</p><p>Feel free to <a href="https://www.linkedin.com/in/dakotaleesmith/">connect with me on LinkedIn</a>. And if you have any questions and/or constructive feedback, don’t hesitate to leave a comment!</p><h3>Level Up Coding</h3><p>Thanks for being a part of our community! Before you go:</p><ul><li>👏 Clap for the story and follow the author 👉</li><li>📰 View more content in the <a href="https://levelup.gitconnected.com/?utm_source=pub&amp;utm_medium=post">Level Up Coding publication</a></li><li>💰 Free coding interview course ⇒ <a href="https://skilled.dev/?utm_source=luc&amp;utm_medium=article">View Course</a></li><li>🔔 Follow us: <a href="https://twitter.com/gitconnected">Twitter</a> | <a href="https://www.linkedin.com/company/gitconnected">LinkedIn</a> | <a href="https://newsletter.levelup.dev/">Newsletter</a></li></ul><p>🚀👉 <a href="https://jobs.levelup.dev/talent/welcome?referral=true"><strong>Join the Level Up talent collective and find an amazing job</strong></a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=27ecc419b063" width="1" height="1" alt=""><hr><p><a href="https://levelup.gitconnected.com/using-python-and-the-terminal-to-simplify-repetitive-data-tasks-27ecc419b063">Using Python and the Terminal to Simplify Repetitive Data Tasks</a> was originally published in <a href="https://levelup.gitconnected.com/">Level Up Coding</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Why is Python Used in Data Analytics?]]></title>
            <link>https://levelup.gitconnected.com/why-is-python-used-in-data-analytics-56b5d3f31cd2?source=rss-dcadf2cebe12------2</link>
            <guid isPermaLink="false">https://medium.com/p/56b5d3f31cd2</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[pandas-dataframe]]></category>
            <category><![CDATA[nlp]]></category>
            <dc:creator><![CDATA[Dakota Smith]]></dc:creator>
            <pubDate>Fri, 02 Sep 2022 16:10:42 GMT</pubDate>
            <atom:updated>2022-10-17T23:32:25.876Z</atom:updated>
            <content:encoded><![CDATA[<h4>Short answer: Because it does pretty much everything</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*5qkJc6h6yr7SPNqh" /><figcaption>Photo by <a href="https://unsplash.com/@artturijalli?utm_source=medium&amp;utm_medium=referral">Artturi Jalli</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><h3>Introduction</h3><p>Python is a popular programming language <a href="https://docs.python.org/3/faq/general.html#:~:text=Details%20here.-,Why%20is%20it%20called%20Python%3F,to%20call%20the%20language%20Python.">named not after the snake, but the sketch comedy group that created Monty Python’s Flying Circus</a>. (Because, well, why not?) It’s known for being easy to learn and read compared to other programming languages, though “easy” is relative to how much programming experience you already have. If you don’t have any at all, and find yourself looking at some Python code for the first time and thinking, “What in the world is that,” rest assured, this is normal.</p><p>People probably thought the same when first watching Monty Python’s Flying Circus.</p><p>Like anything else, reading and writing Python code does eventually get easier. You might remember that once upon a time, SQL also looked rather incomprehensible to our budding data analyst brains. But we’re going to put studying aside for a moment—you won’t find any code samples here—and focus instead on why so many data analysts use Python on a daily basis, and what it can do for you specifically.</p><p>Not every data analyst uses Python. And really, not every data analyst needs to use Python. A lot of what Python can do can be done with other tools such as Excel, Tableau, and low-code integration software.</p><p>However, none of these things can do <em>everything</em> quite like Python can. And arguably, that’s not even its greatest strength.</p><p>It also offers reproducibility. It offers speed and efficiency. It allows for more complex views of data while still being comprehensible to collaborators. (Ever read a SQL query written by someone else that’s 100, 200, 300+ lines long, for example? You usually can’t read it from top to bottom, for one, which can get…pretty confusing, to say the least. And in my experience, those queries are the super important ones you don’t want to mess up. They’re castles made of glass. Large. Fragile. Owned by someone who’s going to be really mad if you break it.)</p><p>But how exactly does it do these things? Why is it requested in so many data-centric job descriptions? And since we’re on the subject, what the heck is a Kaggle Grandmaster?</p><p>In short: It makes your life easier. It does a lot of some things and a good bit of everything else. And don’t worry about Kaggle Grandmasters. You and I, we’re probably never going to become one—which, by the way, is perfectly fine.</p><h3>Automation</h3><h4>The Boring Stuff</h4><p>Yes, that’s a reference to <a href="https://automatetheboringstuff.com/">the book of the same name</a>.</p><p>Both within and beyond the world of data, Python is used to automate the mundane, from renaming files all the way to clicking the <a href="https://github.com/dakotaleesmith/pyProjects/blob/main/baldurs_gate/reroll_stats/reroll_stats.py"><em>Reroll</em> button in Baldur’s Gate’s character creation until your character rolls a total stat over 95</a>. (I spent more time writing that admittedly unimpressive code than I did playing Baldur’s Gate, that day. Eventually I’ll get around to refactoring it. Maybe.)</p><p>A non-exhaustive list of boring stuff Python can automate for you:</p><ul><li>Email</li><li>Filling out PDFs</li><li>Converting files in bulk</li><li>Parsing difficult-to-read text formats</li><li>Dynamically formatting spreadsheets</li><li>Organizing local and remote directories</li><li>Clicking one spot on your screen over and over and over and over</li></ul><p>Automating repetitive tasks is one of Python’s main selling points—or would be, if Python cost anything. Actually, it’s probably fair to say that the more repetitive a task is, the more likely Python can automate it (and the more likely someone already has).</p><p>There are <a href="https://pypi.org/">over 300,000 Python libraries and packages</a> (code that you can import and use in your own programs) on PyPi, the official Python Package Index. It’s more likely than not that something out there can help provide a solution to your monotony. <a href="https://maxnagy.com/posts/pigeons/">This guy</a> wrote a Python script to shoot a water gun at pigeons “voiding their excrements” on his balcony. Not necessarily boring, but hey, you’re not even limited to the digital world.</p><h4>Reporting</h4><p>A lot of summarized reporting nowadays takes place in BI tools like Tableau and/or Excel and within SaaS products themselves. Most of the time, this is fine. But sometimes you have stakeholders who don’t have time to hunt down the report they need, or stakeholders who are used to seeing the data in a certain way. There are also the limitations of the tools themselves to consider—sometimes ease of use comes with reduced functionality. This is especially true when your needs are unique.</p><p>Regardless of the reason, Python can save you the time and inconvenience of having to track down a report yourself, export the data, reformat it accordingly, and send it off to the right people. If this sounds familiar, it should: it’s nothing more than a chain of somewhat mundane tasks.</p><p>But for the well-prepared data analyst, compiling recurring reports <em>should</em> be somewhat boring—it’s only exciting when the incoming data changes for the really good, the really bad, or something on the backend goes wrong. And only one of those is exciting in a positive way.</p><p>Take this scenario for example. On Mondays, the executive team likes a straightforward view (just a simple CSV, thanks) of the previous week’s revenue by region. Your job is simply to query the company database, compile the results, and email it to the execs.</p><p>Let’s say this takes 15 minutes. And let’s say that for 48 weeks out of the year (your company kindly approved you for 4 weeks of PTO), you’re expected to send out this recurring report.</p><p>This task—querying the database, QAing the data and double-checking the query, compiling the data into a CSV, and emailing it to the executive team—could be scripted in Python in about an hour. And no, that’s not generous. Even a beginner could code this with relative ease, and that’s what makes Python particularly compelling.</p><p>Doing so, you save 11 hours annually. And of course that’s just for one report. Every data analyst role is different, but at some companies, you could be doing this sort of thing a lot. Automating your reporting can save you time, impress your bosses, and free you up to focus on more fulfilling and impactful work.</p><h4>Data Pipelines/Web Scraping</h4><p>This ventures into data engineering territory, but data analysts and scientists are sometimes expected to build data pipelines via APIs and with tools like AWS Glue and Lambda, as well as scrape the web for third-party data.</p><p>They’re also sometimes expected to perform ETL, which stands for Extract, Transform, Load. ETL is a process by which data is taken from a source, cleaned and manipulated to fit the needs of the stakeholder, and stored in some other destination. Even though ETL is more commonly thought of as a data engineering task, it can often be the responsibility of analysts, especially at smaller companies.</p><p>A common ETL pipeline might look like this: extract the data from another analytics platform (e.g., Google Analytics), transform and potentially enrich the data so that it can be viewed in new and insightful ways, and then load it into the company’s data warehouse. This is particularly useful when different departments are trying to find correlation or even causation between their efforts and the intended results, the evidence of which can exist in disparate data sources.</p><p>Could you do all of that manually? Possibly. However, not only would it be an inefficient use of time (comparatively), but manual ETL processes introduce a lot of room for error.</p><p>Using libraries like <a href="https://realpython.com/python-requests/">requests</a> and <a href="https://realpython.com/pandas-dataframe/">pandas</a>, Python could automate that entire pipeline. In order to have that pipeline run automatically (without, that is, having to open the script and run it yourself), you’d have to use a job scheduler like cron, or a service like AWS Glue/Lambda. But considering “running it yourself” just means pressing one button, Python still gets you most of the way there.</p><h3>Transformation</h3><h4>Data Cleaning</h4><p>Even if you never officially do any end-to-end ETL your entire data analytics career, you will definitely at least do the T part: Transform. Because if you’re a data analyst, you’re also a data custodian.</p><p>Data cleaning takes many forms. It can be as simple as renaming fields, but as arduous as having to rename hundreds of fields. (Curse you, wide-format data.) It can also be as grueling as locating leading and lagging spaces, or trying to parse deeply nested key-value pairs for a single piece of information.</p><p>Data can, in other words, be messy in a lot of different ways. And in general, as a company’s data grows, so too does the mess. It’s a dog in the mud after a rain. Don’t expect it to find it in an acceptable state.</p><p>When it comes to data cleaning (and really most manipulations of tabular data), pandas is the go-to Python library. If as a data analyst you could only install a single library, there wouldn’t be another contender.</p><p>Pandas is built on top of NumPy, a Python library that is most simply known for its numerical computing capabilities. It allows you to perform virtually any sort of cleaning that you could do in SQL or Excel and then some, only in a more reproducible way. For example, pandas has several built-in functions to handle nested lists and dictionaries within fields, and can conditionally change the names of all of your columns in less than a second. It can also both import and export data from several different file types, including CSV, JSON, and Parquet.</p><p>Pandas isn’t limited to numerical manipulations as the name of its base library might suggest, but can be used to transform string, datetime, and boolean datatypes as well. When used in conjunction with <a href="https://realpython.com/jupyter-notebook-introduction/">Jupyter Notebooks</a> (which allows you to run your code in separate steps), it also gives you the ability to monitor the transformation process in stages as opposed to just running the script and hoping for the best.</p><h4>NLP</h4><p>NLP stands for Natural Language Processing. And what that basically means is figuring out what’s going on in free-form text.</p><p>Not all data is structured, and almost nowhere is that more true than in language. For example, imagine every paragraph in this essay was loaded into a database (each paragraph a single record). Now imagine I cruelly asked you to write a SQL query that determined whether or not each individual paragraph was any good.</p><p>Well, first off, what is good? Maybe you start off by writing a complicated series of nested CASE WHEN statements that ultimately evaluate to true or false (or in this case, good or bad). You might start with: Well, the ideal paragraph is<em> n</em> number of sentences or fewer, so if it goes over that threshold, it’s no good; if it does pass, we can assess readability according to the number of how many high-syllables words were used, and etc…</p><p>But not only is this insane, it’s also likely to oversimplify. A word’s meaning, for example, is highly dependent on context. Even grammar, subject to the steady and transformative march of time, can’t be depended on to produce precise results independent of other variables. I’ve of course proofread this essay, but it’s safe to assume there’s a typo or two lurking in the dark corner of an otherwise innocent-seeming sentence. How should those be evaluated?</p><p>In other words, when it comes to language, it’s really, really difficult to sufficiently take everything into account. If you somehow manage to do it without any help, congratulations, <em>you’re</em> insane. May the history books venerate your brilliance for centuries to come.</p><p>While NLP still has a long way to go, it’s also come quite far. Recently, an AI chatbot developed by Google <a href="https://www.washingtonpost.com/technology/2022/06/11/google-ai-lamda-blake-lemoine/">convinced one of its engineers that it had become sentient.</a> Python NLP libraries can also be quite powerful—not convince-you-it’s-a-sentient-being powerful, but for the purpose of data analytics, powerful enough.</p><p>Libraries such as NLTK, spaCY, and Gensim can be used implement NLP techniques, sometimes in fewer than ten lines of code. One of the more common of these techniques used by data analysts is sentiment analysis, which seeks to determines whether a piece of text is expressing positivity, negativity, or something more neutral. Sentiment analysis is often utilized to discern customer feedback, among other things.</p><h4>Machine Learning</h4><p>Machine learning is:</p><ul><li>A type of artificial intelligence</li><li>In the business of using historical data to predict new outcomes</li><li>Not to be <a href="https://www.wired.com/story/machine-learning-reproducibility-crisis/">applied haphazardly</a></li></ul><p>It’s also something that Python is uniquely well-suited for.</p><p>Machine learning is commonly associated with data science—<a href="https://hbr.org/2012/10/data-scientist-the-sexiest-job-of-the-21st-century">allegedly the sexiest job field of the century</a>, though don’t be surprised if no one else in your family or immediate circle is aware of this. While related to analytics, data science is its own unique endeavor with its own unique challenges.</p><p>However, there’s often overlap when it comes to real-world job responsibilities. For example, depending on the industry, the seniority of the position, and the stage the company is at, data analysts can sometimes be expected to work on predictive modeling implemented via machine learning methods.</p><p>So why is Python well-suited for machine learning? If you guessed libraries, you guessed right. (Keep guessing libraries—you’ll keep being right.) Common Python libraries for ML include Scikit-learn and TensorFlow. Scikit-learn is a more high-level machine learning framework featuring regression, clustering, and classification algorithms, while TensorFlow specializes in deep learning.</p><p>Another benefit to building your machine learning model in Python is platform independence, which means your code can run on different operating systems. Machine learning models developed in Python can be run on different types of local machines as well as through cloud services.</p><p>Machine learning can be deployed with other programming languages, but besides R, these other languages aren’t utilized often in data analytics, and so are unlikely to be in an analyst’s toolkit. Along with the other advantages mentioned, Python makes your machine learning models more reproducible and more open to collaboration with other data analysts and scientists, which is where all the good (human) learning happens.</p><h3>Visualization</h3><p>At the intersection of data and storytelling is good visualization. If that sounds a little cheesy, I offer something a bit more grounded: You can also find visualization at the intersection of the analytics team and upper management. No one—not even the analysts themselves—loves a good chart quite like management.</p><p>Powerful visualization transmogrifies data so that it can be understood in a fraction of the time it would be otherwise. It provides both context and substance in a way that raw numbers often don’t. Think of a time series graph vs. a spreadsheet with last year’s sales aggregated by day—one you could make sense of immediately, the other would cause your eyes to glaze over.</p><p>On the surface, Python may seem like something that’s good for data prep but not data presentation. There’s some truth to this; you’re rarely going to display your code to stakeholders. But Python can actually generate some incredibly customizable visuals. Some libraries, like <a href="https://pypi.org/project/dash/">Dash</a>, even allow you to create interactive dashboards.</p><p>Python will rarely be a simpler solution to business intelligence than tools like Tableau or Power BI. But it can, at times, be more flexible. Libraries like <a href="https://seaborn.pydata.org/">seaborn</a> allow data analysts to quickly generate charts that can be customized in ways that might be tricky in Excel, for example. It can also be helpful in a pinch when the data exists somewhere that your BI tool isn’t currently integrated with.</p><p>You’ll likely spend a lot of time exploring the visualization library’s documentation at first, especially if you need it to do something ultra-specific. But to some degree that’s the nature of coding. This aspect is also part of the fun, arguably—you might go searching for how to add labels to your x-axis and incidentally learn also <a href="https://matplotlib.org/stable/api/markers_api.html">how to turn the dots in your scatter plot into stars</a>. Stars! It’s a thrilling world we live in.</p><p>Or maybe you stumble upon the admittedly simple but stylistically distinctive visualization package, <a href="https://github.com/cutecharts/cutecharts.py">cutecharts</a>. The name is pretty self-explanatory, but this pie chart created with it is perhaps even more so:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/996/1*tLK5odi0nEtSZBejLl-qUQ.png" /><figcaption>I mean, yeah, it’s a cute chart. Did you expect anything different?</figcaption></figure><h3>Conclusion</h3><p>Reading this, you might think: Why bother with BI tools, automated ETL solutions, or third-party analytics platforms? Python can do it all!</p><p>For the most part, it can. But that doesn’t mean it always should. There’s a reason not everyone builds their own house. It takes a lot of time to learn how, a lot of time to plan, and a lot of time to execute the vision. (It also takes money, resources, and probably a fair amount of physical stamina to build a house—but you get the idea).</p><p>I’ve met data analysts who somewhat begrudgingly see Python as something they “should” learn, particularly because it’s so often mentioned in higher-paying job descriptions. But to me, it’s really just about making your life easier where applicable. Python is a tool. Clearly I think it’s worthwhile to learn, but that comes from my personal experience. Whether it’s worth it for someone else depends on a lot of things—the industry they work in, their team’s preferred tech stack, the amount of free time they have to learn. Etc.</p><p>The good news is, in the world of data analytics, Python isn’t going anywhere anytime soon. Actually, the creator of pandas, Wes McKinney, just came out with the third edition of his book, <em>Python for Data Analysis, </em>which you can read online for free <a href="https://wesmckinney.com/book/">here</a>. (Please note that I have no personal or professional affiliation with the book or Wes McKinney. I assure you, he has no idea I exist.)</p><p>So if you’re wondering if it’s too late to start learning: It’s really not.</p><p><em>If you there’s anything you think Python does well that 1) is relevant to data analytics, and 2) I didn’t cover or cover extensively or compellingly enough here, I’d love to hear about it in the comments! And as always, feel free to connect with me on LinkedIn.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=56b5d3f31cd2" width="1" height="1" alt=""><hr><p><a href="https://levelup.gitconnected.com/why-is-python-used-in-data-analytics-56b5d3f31cd2">Why is Python Used in Data Analytics?</a> was originally published in <a href="https://levelup.gitconnected.com/">Level Up Coding</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Break Down a “Hard” SQL Interview Question]]></title>
            <link>https://levelup.gitconnected.com/how-to-break-down-a-hard-sql-interview-question-7325d561857b?source=rss-dcadf2cebe12------2</link>
            <guid isPermaLink="false">https://medium.com/p/7325d561857b</guid>
            <category><![CDATA[big-data-interview]]></category>
            <category><![CDATA[technical-interview]]></category>
            <category><![CDATA[sql-interview-questions]]></category>
            <dc:creator><![CDATA[Dakota Smith]]></dc:creator>
            <pubDate>Thu, 16 Jun 2022 12:12:03 GMT</pubDate>
            <atom:updated>2022-06-21T10:12:28.612Z</atom:updated>
            <content:encoded><![CDATA[<p>In which I walk step-by-step through the process of answering a Yelp SQL interview question labeled “Hard” by StrataScratch</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*M23_OyuYFy-N-81j" /><figcaption>Photo by <a href="https://unsplash.com/@linkedinsalesnavigator?utm_source=medium&amp;utm_medium=referral">LinkedIn Sales Solutions</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><h3>Introduction</h3><p><a href="https://www.stratascratch.com/">StrataScratch</a> is a platform for practicing real interview questions from companies such as Meta, Netflix, Microsoft, and many others. In this article, I’ll be walking step-by-step through the process of answering a SQL interview question from the online directory and review publisher, Yelp.</p><p>If you’re a hands-on learner, you can sign up for a free account and follow along with the coding steps <a href="https://platform.stratascratch.com/coding/10046-top-5-states-with-5-star-businesses?code_type=3">here</a>. Please note that while I like StrataScratch for practicing SQL problems, I have no personal or professional affiliation with the company!</p><p>This article assumes a base level of understanding of SQL fundamentals, such as SELECT, FROM, and WHERE. Documentation for the more intermediate-level functions in the final query will be provided as they’re introduced.</p><h3>The Question</h3><p><strong>Title:</strong> Top 5 States With 5 Star Businesses</p><p><strong>Description:</strong> Find the top 5 states with the most 5 star businesses. Output the state name along with the number of 5-star businesses and order records by the number of 5-star businesses in descending order. In case there are ties in the number of businesses, return all the unique states. If two states have the same result, sort them in alphabetical order.</p><p><strong>Data Schema:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/221/1*h22HNcE5iGIjYzSLBE-hTw.png" /></figure><h4>Step 1: Determine the Deliverables</h4><p>Probably one of the most important SQL skills you can learn is the ability to quickly parse business questions for the pieces that will end up in your final SQL query. In the real world, stakeholders will rarely tell you what to “output” in terms that perfectly match your schema, but the description above still needs to be parsed and translated into SQL.</p><p>Before we write any SQL—before even looking at the data schema, perhaps—we have to make sure we understand what’s being asked. So in the simplest terms, what is Yelp asking for?</p><ul><li>The state name</li><li>The number of 5-star businesses in each state</li></ul><p>That’s it. Yes, the answer must include only the top 5 states, and the records have to be ordered a certain way, but that’s delivery, not the deliverables. And now that we know our deliverables, we can start writing our query.</p><h4>Step 2: Imagine a Perfect World</h4><p>We know our query will have a SELECT and FROM statement. The data has to come from somewhere (a table), and even if we wanted every field from that table, we’d still have to select all of them (using *).</p><p>There’s only one table, yelp_business, so we know that’s where the data is coming from. We also know what we’re trying to select, thanks to figuring out the deliverables beforehand.</p><p>In this step, we’re going to build the foundation by imagining a perfect world:</p><pre>SELECT state, num_businesses <br>FROM yelp_business;</pre><p>If you’re following along or poring over the data schema, you’ll know that this is a “perfect world” because the query returns an error. That’s because <em>num_businesses</em> isn’t a field already present in the data. It’s what we ultimately want, but it doesn’t exist yet. We have to create it.</p><h4>Step 3: Break Down the Logic</h4><p>Steps 1 and 2 are crucial, but Step 3 is where concept meets execution. We’re going to take it slower from here on out.</p><p>There are three logical components to <em>num_businesses</em>:</p><ol><li>It’s a count of something</li><li>That something is 5-star businesses</li><li>And we need that count <strong>by state</strong> (or said more technically, aggregated on the state level)</li></ol><p>Each of these components are factored in different places within the query. Let’s go through them one by one:</p><ol><li><strong>A count of something.</strong> We can return a count of records by adding COUNT(*) to the SELECT statement</li><li><strong>5-star businesses.</strong> Each business’s star rating is found in the <em>stars</em> field. We can filter only for businesses that have a 5-star rating using the WHERE clause: WHERE stars = 5</li><li><strong>Aggregating the counts by state.</strong> To instruct SQL to aggregate (or group) the counts by state, we only need a GROUP BY clause: GROUP BY state</li></ol><p>Put together, it looks like this:</p><pre>SELECT state, COUNT(*) AS num_businesses<br>FROM yelp_business<br>WHERE stars = 5<br>GROUP BY state; </pre><p>Note that I aliased our count as <em>num_businesses</em>. This isn’t just for readability’s sake, though that would be a good enough reason on its own; the aliasing will be important later.</p><p>How to decide what comes next? Well, it comes down to preference. I like to get the simple stuff out of the way. It’s all too easy to forget the small, simple details after overcoming the challenging parts.</p><p>The description tells us to order the results of our query by the number of 5-star businesses (<em>num_businesses</em>) in descending order—i.e., most to fewest. It also tells us that in the place of a tie, the results should order alphabetically by <em>state</em>.</p><p>We can use an ORDER BY clause at the end of our query to achieve this. And since the alphabetization of <em>state</em> only occurs in the case of a tie, <em>num_businesses</em> will come before <em>state:</em></p><pre>SELECT state, COUNT(*) AS num_businesses<br>FROM yelp_business<br>WHERE stars = 5<br>GROUP BY state<br>ORDER BY num_businesses DESC, state</pre><p>Next comes the tricky part. The good thing is, we already have a lot of the business logic translated into SQL. We now only need it to do one more thing: Return only the top 5 states with the most 5-star businesses.</p><p>There is a tempting pitfall to stumble into here. It’s easy at first to think: <em>Okay, simple. Because the results are already ordered according to the number of 5-star businesses, all I have to do is add </em>LIMIT 5<em> and the query will only return the top 5 states.</em></p><p>If we were to run this query, the answer would be incorrect. That’s because of this parameter in the description:</p><blockquote>“In case there are ties in the number of businesses, return all the unique states.”</blockquote><p>Which suggests that it’s possible there’s a tie between states. Upon investigating this, it turns out there’s a 3-way tie for 4th place (to see this, set your LIMIT to 6). When we limit the results to only the top 5 records, the query leaves out that third state tied for 4th.</p><p>How do we get that third state? There’s a few different ways. What you decide on will again depend partly on preference and potentially also on which SQL language you’re using. (I use MySQL.)</p><p>But let’s think about it logically first, because the bigger challenge here is truly understanding the question. When working with stakeholders in the real world, you’ll find that there’s sometimes a gap between the way they word their request and what they’re really asking for. Even in the best-case scenarios, they still most likely don’t know how to word it for you in SQL terms—if they did, they wouldn’t have to come to you.</p><p>The question is asking for the top states according to <strong>how they rank</strong> in number of 5-star businesses. So what we really need here is the ability to rank the states, and then filter on that ranking.</p><p>To do this, I’m going to use a CTE, or Common Table Expression. You could also use a subquery in the FROM statement to the same effect.</p><p>Side-note: It’s my opinion that CTEs <em>sound</em> more intimidating to beginners even though they’re essentially just a way of assigning your subquery to a variable (typically making your code easier to read.) Learning resources also tend to teach subqueries before CTEs, suggesting that the latter is more difficult. Again, it comes down to a matter of preference, but I would argue that CTEs aren’t inherently more difficult or complex than subqueries.</p><p>But why do we need to use either of these things? Because if you stopped at simply adding the rankings using a RANK() function, you wouldn’t be able to filter on it. Let me show you what I mean.</p><pre>SELECT state, COUNT(*) AS num_businesses, RANK() OVER(ORDER BY COUNT(*) DESC) AS state_rank<br>FROM yelp_business<br>WHERE stars = 5<br>GROUP BY state<br>ORDER BY num_businesses DESC, state</pre><p>This query indeed gets us one step closer, but the problem is I can’t add a AND state_rank &lt;= 5 to the WHERE statement. That’s because the WHERE statement doesn’t recognize our calculated fields—if we tried to filter on <em>num_businesses</em>, it would return the same error.</p><p>What we need to do is “flatten” the <em>state_rank</em> field so that the WHERE statement can act on it. We can do this by putting the query above in a CTE.</p><p>The inner-workings of RANK() and CTEs each deserve their own article, and are thus a little beyond the scope of this one. To learn more, read <a href="https://medium.com/towards-data-science/how-to-use-sql-rank-and-dense-rank-functions-7c3ebf84b4e8">here</a> about RANK() and window functions, and <a href="https://medium.com/towards-data-science/take-your-sql-from-good-to-great-part-1-3ae61539e92a">here</a> about CTEs.</p><p>When we run the following query, SQL will first temporarily store the results of the CTE in memory, allowing the rest of the code to execute on that data <strong>as if it were its own table in the database</strong>.</p><p>Again—the CTE here is ultimately nothing more than the <strong>results of the above query</strong>. It’s positioned at the top of the code below:</p><pre>WITH states_with_ranks AS<br>(SELECT state, COUNT(*) AS num_businesses, RANK() OVER(ORDER BY COUNT(*) DESC) AS state_rank<br>FROM yelp_business<br>WHERE stars = 5<br>GROUP BY state)</pre><pre>SELECT state_rank, state, num_businesses<br>FROM states_with_ranks<br>WHERE state_rank &lt;= 5<br>ORDER BY num_businesses DESC, state<br>;</pre><p>Everything after WITH through GROUP BY state) is the CTE. Everything below it is our query. See how the FROM statement in our query references the name of the CTE, states_with_ranks, rather than yelp_business?</p><p>The CTE effectively “flattens” the calculated fields by temporarily storing the results of the calculations in memory. We can then select the <em>state_rank</em> and <em>num_businesses</em> fields by name alone. And most importantly, the WHERE statement then recognizes them as fields that can be filtered on.</p><p>If you’re having trouble understanding the CTE part but understand how subqueries work, it might be helpful to see the above CTE’s <em>exact code</em> repositioned in a subquery inside of the FROM statement:</p><pre>SELECT state_rank, state, num_businesses<br>FROM<br>    (SELECT state, COUNT(*) AS num_businesses, RANK() OVER(ORDER BY COUNT(*) DESC) AS state_rank<br>    FROM yelp_business<br>    WHERE stars = 5<br>    GROUP BY state<br>    ORDER BY state, num_businesses) AS states_with_ranks<br>WHERE state_rank &lt;= 5<br>ORDER BY num_businesses DESC, state<br>;</pre><p>Note as well that in either case, the ORDER BY clause is moved to the very end. This ensures the results are ordered properly.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*ootzc_yhKP4uzSNw" /><figcaption>Photo by <a href="https://unsplash.com/@officestock?utm_source=medium&amp;utm_medium=referral">Sebastian Herrmann</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a>. Thanks, Sebastian!</figcaption></figure><h3>Conclusion</h3><p>Like anything else, the more you translate business logic into SQL, the better you get at it. This is useful in an interview of course, but it’s arguably even more important <em>after</em> you’ve landed the job.</p><p>There are some <a href="https://counting.substack.com/p/we-should-phase-the-sql-interview?utm_campaign=Data_Elixir&amp;utm_source=Data_Elixir_390&amp;s=r">who believe the SQL interview should be phased out</a>, and maybe they’re right. But that’s unlikely to happen anytime soon, and learning how to break down a problem into its composite parts is an invaluable skill no matter what language you’re using.</p><p>Have a question? Let me know in the comments. Feel free to <a href="https://www.linkedin.com/in/dakotaleesmith/">connect with me on LinkedIn</a> as well.</p><p>Oh, and if you have a SQL interview coming up—good luck!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7325d561857b" width="1" height="1" alt=""><hr><p><a href="https://levelup.gitconnected.com/how-to-break-down-a-hard-sql-interview-question-7325d561857b">How to Break Down a “Hard” SQL Interview Question</a> was originally published in <a href="https://levelup.gitconnected.com/">Level Up Coding</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL Querying for Beginners: INNER and RIGHT JOINs]]></title>
            <link>https://levelup.gitconnected.com/sql-querying-for-beginners-inner-and-right-joins-d51125071b16?source=rss-dcadf2cebe12------2</link>
            <guid isPermaLink="false">https://medium.com/p/d51125071b16</guid>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[sql-joins]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[query]]></category>
            <dc:creator><![CDATA[Dakota Smith]]></dc:creator>
            <pubDate>Mon, 18 Apr 2022 01:45:20 GMT</pubDate>
            <atom:updated>2022-04-27T15:46:39.717Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*pcT2EuJ7kp18UG1P" /><figcaption>Photo by <a href="https://unsplash.com/@sunder_2k25?utm_source=medium&amp;utm_medium=referral">Sunder Muthukumaran</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a>. Thanks, Sunder!</figcaption></figure><h3>Introduction</h3><p>JOINs are one of the most common operations used in SQL among data scientists and data analysts. They’re also among the most devastating when misapplied. The wrong choice of JOIN can cause massive duplication, loss of relevant records, and just plain incorrect data.</p><p><a href="https://levelup.gitconnected.com/sql-querying-for-beginners-left-joins-ede3b7959873">In the first essay of this series</a>, we discussed LEFT JOINs. A quick recap of the fundamentals:</p><ol><li>JOINs are how you combine two or more tables into one.</li><li>JOINs work by identifying what tables have in common with each other. They do this using primary and foreign keys.</li><li>A primary key is the unique identifier <strong>for each record</strong> in a table. No two records can have the same primary key value. A primary key can’t be NULL.</li><li>A foreign key in one table is the primary key in some other table. Foreign keys aren’t restrained by the same limitations as primary keys (e.g., duplicate and NULL values).</li><li>A LEFT JOIN brings data from a table “on the right” into a table “on the left”. <strong>All records from the left table remain.</strong> Records from the right table are brought over only when a match is found on the keys.</li></ol><p>The above bullet points aren’t meant to be comprehensive definitions, but a reminder of the concepts explored in <a href="https://levelup.gitconnected.com/sql-querying-for-beginners-left-joins-ede3b7959873">the previous essay</a>. If they don’t make sense, go back and read that first!</p><h3>INNER JOINs</h3><p>Most of the time when using JOINs, you’re going to use either a LEFT JOIN or an INNER JOIN. Though they both perform similar tasks—combining data from different tables—their results are slightly different.</p><p>Where a LEFT JOIN brings over data from the “right” table, leaving behind any records that didn’t find a match in the left table according to the keys, <strong><em>an INNER JOIN leaves behind the records from </em>both<em> tables that didn’t find a match.</em></strong></p><p>Let’s use the same tables from the example in the previous essay: employees and offices.</p><ul><li>The employees table contains data on five different employees at a fictional company.</li><li>The offices table contains data on the fictional company’s four offices.</li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/7a53c75c22bcd73d353c0f6751554d0d/href">https://medium.com/media/7a53c75c22bcd73d353c0f6751554d0d/href</a></iframe><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/0aeae551071ee572ca842f8ea8acd7fe/href">https://medium.com/media/0aeae551071ee572ca842f8ea8acd7fe/href</a></iframe><p>And the code used to LEFT JOIN these tables:</p><pre>SELECT *<br>FROM employees<br>LEFT JOIN offices<br>ON employees.office_code = offices.code<br>;</pre><p>Since the order of the tables determines whether a table is on the left or the right, the above code states that the employees table is the “left” table. When we LEFT JOIN these tables, every record from the employees table stays. Data from the offices table, on the other hand, is brought over wherever <em>offices.code</em> = <em>employees.office_code</em>.</p><p>The result is a list of all employees plus the available information on the offices each person works at:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/2dba17bfc6d3fcf8b2430d785a7e9b05/href">https://medium.com/media/2dba17bfc6d3fcf8b2430d785a7e9b05/href</a></iframe><p>Now let’s re-examine the tables pre-JOIN. If you look closely, you’ll notice that no employee has an <em>office_code</em> = 4. This code corresponds to the Chicago office, but because there was no match on that key, the result of LEFT JOIN didn’t bring over any data related to the Chicago office.</p><p>You might notice as well in the employees table that the Paola Ferrante record doesn’t contain a value in the <em>office_code</em> field. However, Paola’s record is still in the LEFT JOIN result because her data was in the left table.</p><p>In an INNER JOIN, neither table gets such privilege. <strong><em>There must be a match between the keys for a record to be returned in an INNER JOIN.</em></strong></p><p>In our example, the keys are <em>employees.office_code</em> and <em>offices.code</em>. If an employee is missing an office code, they won’t be in the final result. Similarly, if an office code is missing in the employees table—such as in the Chicago office’s case—then data from that office won’t be in the final result either.</p><p>The code for an INNER JOIN is similar to a LEFT JOIN. The difference is only one word:</p><pre>SELECT *<br>FROM employees<br>INNER JOIN offices<br>ON employees.office_code = offices.code<br>;</pre><p>The resulting table, for the reasons mentioned above, doesn’t include Paola Ferrante’s record and also contains no data on the Chicago office:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/124aab03047a4e5ce21b5402114e865f/href">https://medium.com/media/124aab03047a4e5ce21b5402114e865f/href</a></iframe><p><strong><em>Additionally, with INNER JOINs, the order of the tables doesn’t matter.</em></strong> The result is exclusive to records that match on the keys, so “right” and “left” don’t make a difference. The only difference—which admittedly at a glance <em>looks</em> significant—is that the order of the columns are swapped:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/511a4ec1dea2aa7e8b5aae9b4bda4437/href">https://medium.com/media/511a4ec1dea2aa7e8b5aae9b4bda4437/href</a></iframe><p>But the data is otherwise the same.</p><p>A brief note before moving on:</p><p>In the offices table, there’s no value in the <em>phone_number</em> field for the Seattle office (<em>code</em> = 2). This missing value (otherwise known as a NULL value¹) didn’t have an effect on the JOIN. The ON statement, which defined the keys, didn’t tell the JOIN to consider <em>offices.phone_number</em>. It only said to find a match on <em>offices.code =</em> <em>employees.offices_code</em>, which it did.</p><h4>When to use an INNER JOIN over a LEFT JOIN?</h4><p>The underlying schema behind our employees and offices table lends itself logically to LEFT JOINs over INNER JOINs. This is because the offices table is essentially a details table (otherwise sometimes known as a dimension table). The primary use of the offices table would be to bring over details (other data) related to employees at the company.</p><p>So when would you use an INNER JOIN over a LEFT JOIN?</p><p>Generally, the answer is: Any time you only want records that match on the designated keys. But this, to me, begs the question: Why would you want that?</p><p>There are many answers to this, but for now I’m only going to provide one example. Understanding the reasoning behind a single use case is more important than trying to memorize an endless amount of context-dependent scenarios.</p><p>Imagine that our employees and offices tables are much bigger. Instead of 5 employees, there are 5,000. And instead of 4 offices, there are 400—some open and running, some not, and some that are in the works, so to speak. Maybe management knows they’re going to open an office in Dubai, but are still deciding between a few different places.</p><p>Imagine as well that there are hundreds of employees, who, like Paolo Ferrante, don’t have an <em>office_code. </em>They work from home.</p><p>What if you were asked for a list of employees who are currently working in one of the offices? You couldn’t LEFT JOIN offices onto employees—that would give you a list of all employees regardless of where they worked. (You could introduce a WHERE statement that filters out the work-from-home employees, but then you’re adding an extra line. <strong><em>Brevity is clarity in code</em>.</strong>)</p><p>Similarly, you couldn’t LEFT JOIN employees onto offices. This would necessarily return all offices, including the ones that aren’t currently in operation.</p><p>The simplest solution would be to use an INNER JOIN. By doing so, you eliminate any employees that don’t have an <em>office_code</em> and any offices that don’t have employees in them.</p><p>We did exactly this in our INNER JOIN earlier. Paola Ferrante, who presumably works from home, wasn’t included in the result. And the Chicago office—which, according to our data, might well be an empty room with a working telephone—was also left out.</p><h3>RIGHT JOINs</h3><h4>The controversial JOIN</h4><p>I wasn’t sure if I was going to write about RIGHT JOINs. They can be controversial, and because of this, tend to be confusing. But I think they’re worth mentioning.</p><p>For one, RIGHT JOINs aren’t any more conceptually difficult than LEFT JOINs. Second, it’s important to understand that like any other language, SQL has standard conventions that are (most often reasonably) treated as law.</p><p><strong><em>A RIGHT JOIN is essentially the mirror image of a LEFT JOIN.</em></strong> If a LEFT JOIN brings data into the table on the left—while retaining all records from that table—a RIGHT JOIN brings data into the table on the right while retaining all records from <em>that</em> table.</p><p>(It’s important to remember that which table is “on the left” vs. “on the right” is determined by the order of the JOIN. As a reminder: the first table, immediately following FROM in your query, is the left table. The second table, which follows JOIN, is the right table.)</p><p>Let’s look at a RIGHT JOIN using our employees and offices tables. Note that in the code, I’m swapping the order of the tables so that employees is on the right. Like before, I want the records of all employees to be in the result.</p><pre>SELECT *<br>FROM offices<br>RIGHT JOIN employees<br>ON employees.office_code = offices.code<br>;</pre><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/df15f39500dfa49534fc6e911682ae48/href">https://medium.com/media/df15f39500dfa49534fc6e911682ae48/href</a></iframe><p>At a glance, this table might look like something we haven’t seen yet. But if I were to simply change the order of the columns in the SELECT statement (without doing anything to the JOINs), it starts looking a lot more familiar:</p><pre>SELECT employees.*, offices.*<br>FROM offices<br>RIGHT JOIN employees<br>ON employees.office_code = offices.code<br>;</pre><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/2f8c3b9e659f5bc4a577bc15e9d11de8/href">https://medium.com/media/2f8c3b9e659f5bc4a577bc15e9d11de8/href</a></iframe><p>It’s the same result as the table created by our original LEFT JOIN. Any LEFT JOIN can be rewritten to be a RIGHT JOIN, and vice versa. The order of the tables (and the columns if you want it to look <em>exactly</em> the same) need only be reversed.</p><h4>So…should I use LEFT JOINs or RIGHT JOINs?</h4><p>The short and somewhat unsatisfactory answer is: It depends. In the English-speaking world, company best practices will <em>usually</em> dictate that you use LEFT JOINs over RIGHT for readability’s sake. (The exception being long queries involving numerous JOINs, in which case a quick and easy RIGHT JOIN might be preferable to rewriting the entire FROM statement in your code.)</p><p><a href="https://www.edge.org/conversation/lera_boroditsky-how-does-our-language-shape-the-way-we-think">Part of the reason for this is the way we think.</a> English speakers, for example, tend to process information from left to right. As an English speaker, I much preferred the LEFT JOIN of our employees and offices table to the mirror-imaged RIGHT JOIN, because in the latter I had to switch the order of the columns to make sense of what I was seeing—because I process from left to right.</p><p>The ubiquity of this preference (again, specifically in the English-speaking world) has led to the belief that RIGHT JOINs must be bad practice, or at best messy syntax, when in fact they’re functionally no different from LEFT JOINs. There is an argument to be made for readability, however—if your code is unnecessarily confusing to your co-workers or teammates, there’s a higher chance that something will be misunderstood and potentially misapplied.</p><p>My opinion? Know your audience. If you’re unsure but want to go ahead and use a RIGHT JOIN anyway, consider adding a comment in your code that points it out and explains your reasoning.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*BnzZ4ANoP32ReCw3" /><figcaption>Photo by <a href="https://unsplash.com/@rutmiit?utm_source=medium&amp;utm_medium=referral">RUT MIIT</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>LEFT JOINs and INNER JOINs are the two most common JOINs you’ll use when querying as a data analyst or scientist. I hope this essay and the last have been helpful in better understanding how they work.</p><p>Shoutout to <a href="https://www.linkedin.com/in/jrahmlow/">Jeanique Rahmlow</a> for the editorial help, and to the <a href="https://www.dataxp.ai/">Dataxp Discord community</a> for supporting the development of enthusiastic data professionals.</p><p>Have a suggestion for what SQL topic I should explain next? Leave it in the comments! And as always, feel free to <a href="https://www.linkedin.com/in/dakotaleesmith/">connect with me on LinkedIn</a>.</p><p>¹<em>In SQL, you’d actually see the word NULL in the output. As SQL counts spaces as characters, a value that looks blank most likely has a space in it. NULL values, then, should be thought of as truly empty.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d51125071b16" width="1" height="1" alt=""><hr><p><a href="https://levelup.gitconnected.com/sql-querying-for-beginners-inner-and-right-joins-d51125071b16">SQL Querying for Beginners: INNER and RIGHT JOINs</a> was originally published in <a href="https://levelup.gitconnected.com/">Level Up Coding</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL Querying for Beginners: LEFT JOINs]]></title>
            <link>https://levelup.gitconnected.com/sql-querying-for-beginners-left-joins-ede3b7959873?source=rss-dcadf2cebe12------2</link>
            <guid isPermaLink="false">https://medium.com/p/ede3b7959873</guid>
            <category><![CDATA[data-analyst]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[relational-databases]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-analytics]]></category>
            <dc:creator><![CDATA[Dakota Smith]]></dc:creator>
            <pubDate>Tue, 22 Mar 2022 01:19:00 GMT</pubDate>
            <atom:updated>2022-03-22T01:19:00.685Z</atom:updated>
            <content:encoded><![CDATA[<h4>A beginner-friendly explanation of how LEFT JOINs work in SQL</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*vwjUlKJL_7WgrmdL" /><figcaption>Photo by <a href="https://unsplash.com/@adigold1?utm_source=medium&amp;utm_medium=referral">Adi Goldstein</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a>. Thanks, Adi!</figcaption></figure><h3>Introduction</h3><p>JOINs, and LEFT JOINs in particular, are one of the most commonly used operations in SQL. They’re also one of the trickiest to conceptualize when first learning to query.</p><p>Part of the problem is while there is an abundance of resources online with which to teach yourself SQL, a lot of those resources aren’t beginner-friendly. <a href="https://dev.mysql.com/doc/refman/8.0/en/join.html"><strong><em>Official documentation can sometimes be so comprehensive that it’s not conducive to learning the basics.</em></strong></a></p><p>As a data analyst, I work in SQL almost every day. I also spend a fair amount of time explaining technical concepts to people without much technical expertise. By “the basics,” I mean an understanding of what certain SQL functions do on a fundamental level — something that anyone who interacts with a relational database should have, whether they’re in a highly technical role or not.</p><p>Which is why I’ve decided to write a series centered around SQL querying for beginners, starting with this essay on LEFT JOINs. <strong><em>The aim of the series is to make common SQL concepts as simple and concrete as possible while still being a useful technical reference.</em></strong> I might one day go back and explain the essential building blocks of query syntax — <em>SELECT</em>, <em>FROM</em>, and <em>WHERE</em> — but for now, this article assumes you understand at lease these fundamentals.</p><h3>The Concept of JOINs</h3><h4>What is a JOIN?</h4><p>In a SQL database, you’ll most often be dealing with multiple tables. <strong><em>JOINs are how you combine two or more tables into one.</em></strong></p><h4>Why would you use a JOIN?</h4><p>There are several reasons you’d use a JOIN. Those reasons include, but are not limited to:</p><ul><li>Adding columns from one table to another</li><li>Looking only at records — say, a list of customers — that all have something in common</li><li>Setting up a table for aggregations that wouldn’t otherwise be possible</li></ul><p>If these reasons don’t make perfect sense yet, that’s okay. The point here is that JOINs can be used for many different purposes. <strong><em>Whenever you need to look at data that isn’t already organized in a single table, you use a JOIN.</em></strong></p><h4>But how do JOINs work?</h4><p>This is where conceptualizing JOINs starts to get difficult. I’m going to summarize the answer to this question in non-technical terms first, then swap out those terms for their technical counterparts.</p><p><strong><em>On its simplest level, a JOIN works by deciding what a row in one table has in common with a row in another. Based on this relationship, information from the row in the second table can be added to the row in the first.</em></strong></p><p>A quick step-by-step example before swapping the terms:</p><ol><li>One table describes specialty pizzas at a restaurant. The first column contains the name of a pizza. The other two columns in this table contain the type of cheese and type of sauce. Each row describes a single pizza.</li><li>A second table describes the toppings on the restaurant’s pizzas. The first column contains the name of the pizza. The second column contains a list of that pizza’s specialty toppings.</li><li>Names for each of the restaurant’s pizzas appear in both tables. <strong><em>By identifying what these two tables have in common, a JOIN can be performed.</em></strong> The result of this could be, for example, a single table that contains the name of the pizza, the type of cheese, type of sauce, and any and all toppings, each row representing one specialty pizza.</li></ol><p>That’s essentially it. Really.</p><p>Now for the more technical definition:</p><p><strong><em>JOINs work by identifying foreign and primary keys in two or more tables. The relationship between these keys determines how the tables are combined.</em></strong></p><p>The technical definition might’ve just scrambled your brain a little, but trust me, it’s a lot less complicated than it sounds. It’s one of those <em>When you see it, you’ll know</em> things.</p><p>So let’s see a JOIN in action.</p><h3>LEFT JOINs</h3><h4>What does “left” have to do with anything?</h4><p>LEFT JOINs are one of if not the most common type of JOINs you’ll work with as a data analyst or scientist. Of course, that doesn’t explain what they are.</p><p>Picture the tables below as side by side. The first table is on the left. The second is on the right.</p><p>Table #1:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/7a53c75c22bcd73d353c0f6751554d0d/href">https://medium.com/media/7a53c75c22bcd73d353c0f6751554d0d/href</a></iframe><p>Table #2:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/0aeae551071ee572ca842f8ea8acd7fe/href">https://medium.com/media/0aeae551071ee572ca842f8ea8acd7fe/href</a></iframe><p>In a LEFT JOIN, table #1 — the table on the left — can be thought of as the base. This means that unless specified otherwise in a different part of the query, <strong><em>everything in table #1 is going to look the same in the joined table.</em></strong></p><p>Now for the actual data:</p><ul><li>Table #1 contains data on five fictional employees at a fictional company, and is called “employees”.</li><li>Table #2 contains data on the company’s four offices and is called “offices”.</li></ul><p>When we perform a LEFT JOIN on these two tables, the data in table #2 will “move left” to merge with the data in table #1.</p><h4>Okay but what was that about primary and foreign keys?</h4><p><strong><em>A primary key is a unique identifier.</em></strong> Each record can only have one primary key. Every primary key is different.</p><p>In the first table, the primary key is <em>employee_id</em>. Each employee has one ID. No two employees can have the same ID.</p><p>In the second table, the primary key is <em>code</em>. Same deal — every office has one code and no two offices can have the same code.</p><p>A foreign key is the same concept but with one caveat. <strong><em>A foreign key in one table is the primary key in some other table.</em></strong></p><p>In our example, there’s only one foreign key. It’s the <em>office_code</em> field in the employees table.</p><ul><li>Note that while every office can only have one <em>code</em>, more than one employee can have the same <em>office_code</em> (because they work in the same office). This non-uniqueness is a defining feature of foreign keys.</li></ul><p>When performing a join, you must specify the primary and foreign key in the SQL query. You also tell the query which table should be considered the base table (or left table) by ordering it first in the code. We’ll see how in just a second, but these two attributes determine how a JOIN works.</p><h4>So what’s this look like in practice?</h4><p>In this case, pretty simple. Ultimately, JOINs are just a combination of tables, so in SQL, <strong><em>the JOIN clause is inserted in the same place as the table name: in the FROM statement.</em></strong> Specifically, it goes underneath.</p><p>Without selecting only certain columns, a LEFT JOIN performed on our employees and offices tables is written like this:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/782f9f5ffd733f06c5889ee58c183cec/href">https://medium.com/media/782f9f5ffd733f06c5889ee58c183cec/href</a></iframe><p>Since this is a LEFT JOIN, the first table in the order is considered the “left” table, or the base table, and stays exactly the same. <strong><em>The ON portion of the JOIN statement tells SQL what the two tables have in common (their respective keys) and how the data should be brought together.</em></strong></p><p>The result of the above query, for example, is this:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/2dba17bfc6d3fcf8b2430d785a7e9b05/href">https://medium.com/media/2dba17bfc6d3fcf8b2430d785a7e9b05/href</a></iframe><p>Our second table, the offices table, merged with the employees table according to the ON statement. Specifically, the ON statement told SQL that in this join, every <em>office_code</em> in the employees table had to equal <em>code </em>in the offices table.</p><p>Wherever that was the case, the office’s various data points — city, state, and phone number — were integrated into the joined table. For example, we can now see that John Smith works in Nashville.</p><p>A few other things to note about the left-joined table:</p><ul><li>In the original employees table, the record for Paola Ferrante from Human Resources doesn’t have a value in the <em>office_code</em> field. Because of the ON statement, which said that every <em>office_code</em> in the employees table must equal a <em>code</em> in the offices table, no office data for Paola could be brought over.</li><li>Data corresponding to the New York office appears in the joined table twice. This is also because of the ON statement. When the LEFT JOIN was performed, any time it found an <em>office_code</em> = 3 (the New York office’s <em>code</em>) in the employees table, the corresponding data was brought over. Since there are two employees with an <em>office_code</em> = 3, the New York office data in the joined table is found at the end of both of those rows.</li><li>The original offices table listed a fourth office in Chicago, <em>code</em> = 4. However, no one in the employees table has an <em>office_code</em> = 4. In that scenario, no data describing the Chicago office appears in the joined table. <strong><em>LEFT JOINs leave behind records in the “right” table that don’t find a match in the “left” table.</em></strong></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*_7HX0W5bccdnjUFb" /><figcaption>Photo by <a href="https://unsplash.com/@napr0tiv?utm_source=medium&amp;utm_medium=referral">Vasily Koloda</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>Make sense? If so, good news: You’re past the hard part. The other two most common JOINs — INNER and OUTER — work similarly. They’ll be the focus of the next part of this series.</p><p>Shoutout to <a href="https://www.linkedin.com/in/jrahmlow/">Jeanique Rahmlow</a> for the editorial help, and to the <a href="https://www.dataxp.ai/">Dataxp Discord community</a> for supporting the development of enthusiastic data professionals. Feel free to <a href="https://www.linkedin.com/in/dakota-smith-878243217/">connect with me on LinkedIn!</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ede3b7959873" width="1" height="1" alt=""><hr><p><a href="https://levelup.gitconnected.com/sql-querying-for-beginners-left-joins-ede3b7959873">SQL Querying for Beginners: LEFT JOINs</a> was originally published in <a href="https://levelup.gitconnected.com/">Level Up Coding</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How Gilmore Girls Uses Inefficient Storytelling to Tell a Story Effectively]]></title>
            <link>https://writingcooperative.com/how-gilmore-girls-uses-inefficient-storytelling-to-tell-a-story-effectively-a80844e135e8?source=rss-dcadf2cebe12------2</link>
            <guid isPermaLink="false">https://medium.com/p/a80844e135e8</guid>
            <category><![CDATA[fiction-writing]]></category>
            <category><![CDATA[writing-tips]]></category>
            <category><![CDATA[gilmore-girls]]></category>
            <category><![CDATA[writing]]></category>
            <category><![CDATA[storytelling]]></category>
            <dc:creator><![CDATA[Dakota Smith]]></dc:creator>
            <pubDate>Thu, 17 Mar 2022 13:03:10 GMT</pubDate>
            <atom:updated>2022-07-16T14:29:16.182Z</atom:updated>
            <content:encoded><![CDATA[<h4>Writing “rules” often exist for good reason. But it’s also good, every once in a while, to see those rules broken effectively.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*4tuES0XtwB_OtBSb" /></figure><p>If you’ve never watched Gilmore Girls, there are three things the show is best known for: its lovable characters, quippy dialogue, and Stars Hollow, <a href="https://www.theguardian.com/tv-and-radio/2016/nov/05/sunny-safe-gilmore-girls-comfort-tv">one of the most charming settings in all of television</a>.</p><p>Stars Hollow, where the majority of the show takes place, is a fictional town in Connecticut that exemplifies the notion of small town charm. Along with being adorable, its residents are usually trying to do right by each other. Disagreements often diffuse themselves by way of good communication. A lot of time is spent discussing food. A lot more time is spent discussing coffee.</p><p>A setting like this might not sound ideal for writing several seasons of mostly non-episodic television, but that’s only if a story’s setting is thought of as the pressure cooker with the sole purpose of bringing high-stakes conflict to a boil.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*KJnaAsTFmDeoBN6V" /><figcaption>The only thing brought to a boil in Gilmore Girls: coffee</figcaption></figure><p>In the ninth episode of the third season, Lorelai and her daughter, Rory, find themselves in the predicament of being overbooked on Thanksgiving. This is about as low-stakes as it can get; out of their four obligations, only one would result in what most shows would consider serious consequences if missed. The others—maybe some feelings would be hurt, but not terribly, so no biggie, right?</p><p>Not exactly. In Gilmore Girls, other people’s feelings are treated genuinely as serious consequences. For example, Lorelai and Rory’s predicament probably could’ve been solved by skipping one Thanksgiving event in particular, the dinner at Luke’s diner. But when Lorelai notices Luke’s disappointment at this suggestion, she immediately dismisses the possibility. Disappointing friends isn’t an option. Lorelai—fatally self-aware—even acknowledges this aloud in the moment.</p><p>Notably, none of the episode’s conflict is derived from Lorelai and Rory being overbooked. In fact, they hardly face a single obstacle preventing them from attending any of the four dinners (except perhaps being really, really full, but considering they later request dinner rolls “for the road” from Luke, it’s hard to make too much of this complaint).</p><p>There’s not really much conflict in the episode at all*, which is where I think things get interesting from a writing perspective. <a href="https://www.masterclass.com/articles/how-to-write-the-perfect-scene#the-8-elements-of-a-good-scene">Writers are constantly told nowadays</a> that a scene <a href="https://quotidianwriter.medium.com/the-anatomy-of-writing-a-scene-f18bdbb0215">needs to be doing eight different things</a> in order to avoid the cutting room floor. If the conversation between your main character and their mother <a href="https://www.advancedfictionwriting.com/articles/writing-the-perfect-scene/">isn’t showing character growth</a> while also <a href="https://noamkroll.com/the-5-critical-elements-every-film-scene-hinges-on/">expounding upon the story’s main themes</a> and <a href="https://www.screenwritersuniversity.com/pages/five-characteristics-1">raising the stakes halfway to life-and-death</a>, it either needs to be revised accordingly or taken out altogether.</p><p>*(There are a couple of exceptions to this—the dinner at the Gilmores’ and the scene between Dean and Jess—but not only do both of these come at the end, they’re also, in my opinion, not the most engaging parts of the episode.)</p><p>The writing in Gilmore Girls doesn’t subscribe to this thinking. In the same episode, multiple scenes feature a Stars Hollow resident named Kirk describing his experiences with his new cat (named, I have to add, <a href="https://gilmoregirls.fandom.com/wiki/Cat_Kirk">Cat Kirk</a>). We never see the cat. The cat doesn’t become relevant later on. Kirk simply tells us about her—or him. He’s not sure, at first.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*hoskql80ITDxNV88" /><figcaption>Cat Kirk, probably</figcaption></figure><p>Character-building might be a precursor to plot, but these the Cat Kirk don’t ultimately lead to anything. (Ever, actually.) They seemingly exist for no other reason than to be enjoyable while also, I suppose, validating our understanding of this person. Naming a cat after himself? Totally on brand for this guy.</p><p>Contemporary writing advice suggests that telling a story effectively means telling it efficiently. So what are those scenes—and all the others like it in Gilmore Girls—doing? And more importantly, why do they work so well?</p><p>The fun tangents in Gilmore Girls that don’t amount to much are some of my favorite moments in the show because they make the show feel more real. There <em>is</em> a longer narrative going on in Gilmore Girls, it just so happens that outside of that narrative there are also unrelated things going on—like in life.</p><p>The effectiveness of scenes like this also suggests that character- and world-building don’t have to be explored only at the beginning of a story, and that both of these things can be just as compelling as progression through the plot. Perhaps especially when your characters and setting are already vivid, spending time to iterate on that—sure, let’s give Kirk an aggressive cat that scares him so much, he has to announce himself before entering any room in his own house—can reap a lot of reward.</p><p>But most of all, I think the biggest takeaway is that narrative efficiency isn’t necessarily as important as understanding what makes your story special. An episode of Gilmore Girls centered around the people of Stars Hollow just being themselves works because that particular quality is the draw of the show in the first place. This premise wouldn’t work for every story, of course. But that’s the point.</p><p>It helps to be a popular television show with a large and devoted following, many of whom would happily give their opinion on what makes the show special to them. (Here I am, doing exactly that.) But Gilmore Girls never feels like it’s leaning into what it does best simply to pander to its audience’s expectations. It feels like the show focuses on those aspects because that’s what it enjoys most about itself.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*cdGfD8gQajg5TXMq" /><figcaption>As soon as I crack the code, my next article will be how to amass a large and devoted following</figcaption></figure><p>To be clear, there’s nothing wrong with writing efficiently. Writing “rules” often exist for good reason. But it’s also good, every once in a while, to see those rules broken effectively. Especially when you get to spend the time in a a place as charming as Stars Hollow.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a80844e135e8" width="1" height="1" alt=""><hr><p><a href="https://writingcooperative.com/how-gilmore-girls-uses-inefficient-storytelling-to-tell-a-story-effectively-a80844e135e8">How Gilmore Girls Uses Inefficient Storytelling to Tell a Story Effectively</a> was originally published in <a href="https://writingcooperative.com/">The Writing Cooperative</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>