https://www.datacamp.com/community/podcast/successful-digital-transformation-puts-people-first

<p><em>Bhavin Patel, Director of Analytics and Innovation at Johnson &amp; Johnson, discusses how to drive success with digital and data transformation, why investing in people is the most important aspect of becoming a digital-first organization, how data science accelerates outcomes in supply chain, and more.</em></p>

Bhavin Patel, Director of Analytics and Innovation at Johnson & Johnson, discusses how to drive success with digital and data transformation and more.

Successful Digital Transformation Puts People First

https://www.datacamp.com/blog/successful-digital-transformation-puts-people-first

https://www.datacamp.com/community/podcast/the-gradual-process-of-building-a-data-strategy

<p>Vijay Yadav, Director of Quantitative Sciences &amp; Head of Data Science at Center for Mathematical Sciences at <a target="_blank" rel="noopener noreferrer" href="https://www.linkedin.com/company/merck/">Merck</a> breaks down the different pillars of a scalable data strategy.</p>

Vijay Yadav, Director of Quantitative Sciences & Head of Data Science at Center for Mathematical Sciences at Merck breaks down the different pillars of a scalable data strategy.

The Gradual Process of Building a Data Strategy

https://www.datacamp.com/blog/the-gradual-process-of-building-a-data-strategy

https://www.datacamp.com/community/news/creating-smart-cities-with-data-science-ofs0psrh048

Amen Ra Mashariki deep dives into the state of data literacy in government agencies, the use cases he worked on to make the City of New York smarter, and more!

Creating Smart Cities With Data Science

https://www.datacamp.com/community/news/the-credibility-crisis-in-data-science-with-skipper-seabold-6bbmgpwy1qm

Skipper Seabold, a Director of Data Science at Civis Analytics, discusses the current and looming credibility crisis in data science.

The Credibility Crisis in Data Science (with Skipper Seabold)

https://www.datacamp.com/community/news/sql-tutorial-how-to-write-better-queries-d2nj398ov1l

Structured Query Language (SQL) is an indispensable skill in the data science industry and generally speaking, learning this skill is fairly easy. However, most forget that SQL isn't just about writing queries, which is just the first step down the road. Ensuring that queries are performant or that 

SQL Tutorial: How To Write Better Queries

https://www.datacamp.com/community/blog/how-data-culture-unlock-digital-transformation

Chief Data & Innovation Officer at Gulf Bank, Mai AlOwaish, outlines how a people and data-first approach can unlock the value of digital transformation for organizations across industries.

How Data & Culture Unlock Digital Transformation

<center><a target="_blank" href="https://www.datacamp.com/groups/business"><img src="https://res.cloudinary.com/dyd911kmh/image/upload/f_auto,q_auto:best/v1624651228/schedule_a_demo_bfhlx1.png" /></a></center><br>
<p>In a recent episode of <a target="_blank" href="https://www.datacamp.com/community/blog/the-data-storytelling-skills-data-teams-need">DataFramed</a>, Andy Cotgrave, technical evangelist at Tableau, shared the importance of data storytelling in driving change with analytics. In this two-part blog post, we deep dive into seven concrete tips that Andy provided on telling better data stories.</p>
<p>Read <a target="_blank" href="https://www.datacamp.com/community/blog/seven-tricks-for-better-data-storytelling-1">Part I</a> if you haven't already!</p>
<h2 id="4-use-a-narrative-structure-in-data-stories-">4. Use a narrative structure in data stories.</h2>
<p>Stories generally follow a common arc – the protagonist faces a challenge, goes on to resolve it, then returns to normalcy. An example of this arc is the Hero’s Journey.</p>
<center><img src="https://res.cloudinary.com/dyd911kmh/image/upload/v1643320567/image8_grp6ly.png" />Figure 1: The hero’s journey</center><br>

<p>The audience is <a target="_blank" href="https://www.forbes.com/sites/kateharrison/2015/01/20/a-good-presentation-is-about-data-and-story/?sh=7599eca6450f">22 times more likely</a> to remember a fact when told a story. Clearly, data visualizations stand to benefit from adopting the age-old narrative structure.</p>
<p>Adopting the narrative structure, a data story starts with a background on the current situation, continues with evidence that builds up to the central insight, and ends with a call to action. (Figure 2)</p>
<center><img src="https://res.cloudinary.com/dyd911kmh/image/upload/v1643320742/image4_nnm9k9.png" />Figure 2. The narrative structure of a data story</center><br>


<p>An excellent example of a data story, the <a target="_blank" href="https://www.gatesfoundation.org/goalkeepers/report/2021-report/#LongTermInvestmentsPayOff">Goalkeeper’s Report by the Gates Foundation</a> demonstrated how the pandemic exacerbated inequality. (Figures 3A, 3B, and 3C)</p>
<br><center><img src="https://res.cloudinary.com/dyd911kmh/image/upload/v1643320251/poverty_s2wd18.gif" />Figure 3A: To set the scene, the data story first established the impact of COVID-19 on poverty-stricken countries. [<a target="_blank" href="https://www.gatesfoundation.org/goalkeepers/report/2021-report/#LongTermInvestmentsPayOff">Source</a>]</center><br>

<center><img src="https://res.cloudinary.com/dyd911kmh/image/upload/v1643320508/image7_bmdtdx.png" />Figure 3B: The data story provided rising insights by highlighting the widening chasm between the haves and the have-nots pre-pandemic and post-pandemic.</center><br>

<center><img src="https://res.cloudinary.com/dyd911kmh/image/upload/v1643319219/image2_lyw5ro.png" />Figure 3C: The data story closes with a call to action–for greater investments in women, communities, and innovations.</center>

<h2 id="5-adapt-data-stories-to-the-medium">5. Adapt data stories to the medium</h2>
<p>It is tempting to reuse existing charts across multiple mediums. Data storytellers who do so are doing themselves a disservice.</p>
<p>Charts that are not optimized for their medium might fail to deliver their message effectively. A chart originally designed for email might contain minuscule texts that cannot be read on a screen.</p>
<p>The Economist portrayed how data stories can be adapted from print media to social media. The printed chart (designed for serious readers) was dense in information. It was streamlined for Instagram (designed for casual scrollers).</p>
<br><center><img src="https://res.cloudinary.com/dyd911kmh/image/upload/v1643320591/image5_qjtkiu.png" />Figure 4: The Economist’s printed chart (left) is adapted for social media (right) [<a target="_blank" href="https://medium.economist.com/charting-new-territory-7f5afb293270">Source</a>]</center>

<h2 id="6-declutter-your-data-stories">6. Declutter your data stories</h2>
<p>Clutter makes data stories harder to read. The additional cognitive load distracts the audience from the main message.</p>
<p>The onus is on the data storyteller to identify and remove superfluous elements that do not contribute to the intended message. The result of decluttering is striking.</p>
<p>In particular, our whitepaper “<a target="_blank" href="https://www.datacamp.com/resources/whitepapers/8-rules-for-better-data-storytelling">8 Rules for Better Data Storytelling</a>” suggests data storytellers to:</p>
<ul>
<li>Remove chart borders</li>
<li>Remove gridlines or axes</li>
<li>Clean up axis labels</li>
<li>Label data directly (as opposed to using a legend)</li>
<li>Remove data markers</li>
<li>Use special effects (bold, underline, italics, shadows) sparingly</li>
</ul>
<h2 id="7-explain-data-stories-in-stages">7. Explain data stories in stages</h2>
<p>When presenting complex data stories, one practical tip is to reveal the elements of the chart gradually. When the information is presented in digestible pieces, the audience is more likely to engage with the presentation.</p>
<p>A prime example is the talk “<a target="_blank" href="https://www.youtube.com/watch?v=jbkSRLYSojo">The Joy of Stats</a>” by Hans Rosling. Watch how Rosling masterfully told his data story.</p>
<iframe width="560" height="315" src="https://www.youtube.com/embed/jbkSRLYSojo" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>
<h2 id="data-storytelling-is-here-to-stay">Data storytelling is here to stay</h2>
<p>Data storytelling will soon <a target="_blank" href="https://www.forbes.com/sites/brentdykes/2020/01/22/why-data-storytellers-will-define-the-next-decade-of-data/?sh=56a32d24fb9c">become a necessary skill set</a> for all in the coming decade as data insights become a cornerstone of organizations. To learn more tips and tricks on data storytelling, listen to Andy’s podcast, or learn from his most recent webinar on DataCamp.</p>
<br><center><a target="_blank" href="https://www.datacamp.com/groups/business"><img src="https://res.cloudinary.com/dyd911kmh/image/upload/f_auto,q_auto:best/v1624651228/schedule_a_demo_bfhlx1.png" /></a></center>

https://www.datacamp.com/community/blog/seven-tricks-for-better-data-storytelling-2

Seven Tricks for Better Data Storytelling: Part II

<h2>Retrieving Data From The Database</h2><p>This chapter provides a brief introduction to working with relational databases in a marketing context. You'll learn about their structure,  how to talk about them using database lingo, and how to begin an  analysis by using simple SQL commands to select and summarize columns  from database tables.</p><h2>Filter Data And Limit Your Output</h2><p>In this chapter, you'll learn how to filter tables for rows that satisfy your criteria of interest with the help of basic comparison operators and combinations of multiple criteria.</p><h2>What's Next?</h2><p>Besides selecting columns and filtering rows, there is much more that you can do with SQL! This chapter will present you with some examples that will motivate you to continue to develop this newly gained skill!</p>

https://www.datacamp.com/community/open-courses/sql-tutorial-for-marketers

The best marketers and growth hackers are data-driven. Learn how to use Structured Query Language (SQL) to query a database containing bank clients and marketing campaigns data.

SQL Tutorial for Marketers

https://www.datacamp.com/community/tutorials/tutorial-how-to-execute-sql-queries-in-r-and-python

Learn easy and effective ways to run SQL queries in Python and R for data analysis and database management. 

How to Execute SQL Queries in Python and R Tutorial 

<p>As a data scientist or data analyst, data is naturally at the heart of the job. There are many sources from which you might gather your data. Often the data lives in a SQL database, in which case an understanding of SQL query commands can be essential in order to perform this role successfully. This article will introduce you to some of the more basic commands, right up to more advanced operations that will serve you well when working as a data analyst or data scientist.</p>
<nav><ul>
<li><a href="#simple-data-retrieval">Simple Data Retrieval</a>
<ul>
  <li><a href="#select-from">SELECT FROM</a></li>
<li><a href="#distinct">DISTINCT</a></li>
</ul>
</li>
<li><a href="#data-retrieval-with-simple-conditions">Data Retrieval with Simple Conditions</a>
<ul>
  <li><a href="#where">WHERE</a></li>
<li><a href="#order-by">ORDER BY</a></li>
<li><a href="#limit">LIMIT</a></li>
</ul>
</li>
<li><a href="#aggregations">Aggregations</a>
<ul>
  <li><a href="#group-by-count-">GROUP BY &amp; COUNT( )</a></li>
<li><a href="#sum-">SUM( )</a></li>
<li><a href="#avg-having">AVG( ) &amp; HAVING</a></li>
<li><a href="#min-alias">MIN( ) &amp; Alias</a></li>
</ul>
</li>
<li><a href="#joins">Joins</a>
<ul>
  <li><a href="#inner-join-join-">INNER JOIN (JOIN)</a></li>
</ul>
</li>
<li><a href="#changing-data-types">Changing Data Types</a>
<ul>
  <li><a href="#cast-">CAST( )</a></li>
<li><a href="#round-">ROUND( )</a></li>
</ul>
</li>
<li><a href="#complex-conditions">Complex Conditions</a>
<ul>
  <li><a href="#case-statement">CASE Statement</a></li>
<li><a href="#subqueries">Subqueries</a></li>
<li><a href="#common-table-expressions-ctes-">Common Table Expressions (CTEs)</a></li>
<li><a href="#window-functions">Window Functions</a></li>
</ul>
</li>
</ul>
<p>&nbsp;</p></nav>
<p>In order to understand the commands listed above, we are going to use the open source DVD rental database to show you how these commands can be used. You can find the entity relationship diagram (EDR) <a target="_blank" href="https://www.postgresqltutorial.com/wp-content/uploads/2018/03/printable-postgresql-sample-database-diagram.pdf">here</a>.</p>
<p>For the purpose of this article, we have set up the database locally and completed all queries using <a target="_blank" href="https://www.enterprisedb.com/downloads/postgres-postgresql-downloads">pgAdmin4</a>, so we are using postgreSQL. Many if not all the commands should work in other SQL servers. The <a target="_blank" href="https://www.datacamp.com/community/tutorials/installing-postgresql-windows-macosx">installation of postgreSQL</a> and the setting up of the database itself is outside the scope of this article; we encourage you to check out other resources such as DataCamp's <a target="_blank" href="https://www.datacamp.com/community/tutorials/beginners-introduction-postgresql?">Beginner&#39;s Guide to PostgreSQL</a> for advice on these processes.</p>
<h2 id="simple-data-retrieval">Simple Data Retrieval</h2>
<h3 id="select-from">SELECT FROM</h3>
<p>The simplest example of retrieval is to get all the content from a specific table in the database.
If we wanted to know all the film categories that are in the 'Category' table, you could try inputting this command:</p>
<pre><code class="lang-SQL hljs">SELECT *
FROM category
</code></pre><p>Which returns (only the first 10 rows shown):</p>
<center><img src="https://res.cloudinary.com/dyd911kmh/image/upload/v1643102294/image9_zyfudf.png" alt="SELECT FROM"></center>


<p>Here we've specified the table name after FROM. Since we want all the content in the table, we've used * after SELECT to specify that we want everything.</p>
<h3 id="distinct">DISTINCT</h3>
<p>There are cases in which we are only interested in knowing unique values. For example, what if we want to know all the MPAA ratings from the film table? Using <b>DISTINCT</b> allows us to choose unique values alone:</p>
<pre><code class="lang-SQL hljs">SELECT DISTINCT(rating)
FROM film
</code></pre><p>Which returns:</p>
<center><img src="https://res.cloudinary.com/dyd911kmh/image/upload/v1643102293/image4_nyxfur.png" alt="DISTINCT"></center>

<h2 id="data-retrieval-with-simple-conditions">Data Retrieval with Simple Conditions</h2>
<h3 id="where">WHERE</h3>
<p>There are also situations in which we only want to retrieve specific entries that meet specific conditions. We can provide conditions that have to be met before returning the data using the <b>WHERE</b> clause. In essence, we are filtering entries given the condition. For example, what if we want to know only movies that are rated PG-13?</p>
<pre><code class="lang-SQL hljs">SELECT title
FROM film
WHERE rating = &#39;PG-13&#39;
</code></pre><p>Which returns (only the first 10 rows shown):</p>
<center><img src="https://res.cloudinary.com/dyd911kmh/image/upload/v1643102295/image12_zqzlbh.png" alt="Data Retrieval with Simple Conditions 1"></center>

<p>Additionally, we can figure out the list of movies that have a rental rating of 4.5 or higher:</p>
<pre><code class="lang-SQL hljs">SELECT title, rental_rate
FROM film
WHERE rental_rate &gt;= 4.5
</code></pre><p>Which returns (only the first 10 rows shown):</p>
<center><img src="https://res.cloudinary.com/dyd911kmh/image/upload/v1643102294/image7_fvoyrh.png" alt="Data Retrieval with Simple Conditions 2"></center>

<h3 id="order-by">ORDER BY</h3>
<p>Ordering the returned result is often useful for us to understand the data better. How about if we want the same list above with PG-13 movies, but ordered by the movie length from longest to shortest, and the corresponding movie length? Using <b>ORDER BY</b>, this is easily accomplished:</p>
<pre><code class="lang-SQL hljs">SELECT title, length
FROM film
WHERE rating = &#39;PG-13&#39;
ORDER BY length DESC
</code></pre><p>Which returns (only the first 10 rows shown):</p>
<center><img src="https://res.cloudinary.com/dyd911kmh/image/upload/v1643102294/image10_cklqha.png" alt="ORDER BY"></center>

<h3 id="limit">LIMIT</h3>
<p>Additionally, sometimes we are only interested in a limited number of entries. Here, we are actually only interested in the top 10 longest movies that are PG-13, so we can use the <b>LIMIT</b> clause.</p>
<pre><code class="lang-SQL hljs">SELECT title, length
FROM film
WHERE rating = &#39;PG-13&#39;
ORDER BY length DESC
LIMIT 10
</code></pre><p>Which returns:</p>
<center><img src="https://res.cloudinary.com/dyd911kmh/image/upload/v1643102294/image8_h5jren.png" alt="LIMIT"></center>

<h2 id="aggregations">Aggregations</h2>
<h3 id="group-by-count-">GROUP BY &amp; COUNT( )</h3>
<p>Aggregations are often used to get a summary of the dataset to gain insights. Aggregations are often used with the <b>GROUP BY</b> clause. For example, if we need to know how many rentals each customer has rented so far, we can count the number of rentals and order the results with the most rentals on top.</p>
<pre><code class="lang-SQL hljs">SELECT customer_id, COUNT(rental_id)
FROM rental
GROUP BY customer_id
ORDER BY COUNT(rental_id) DESC
</code></pre><p>Which returns (only the first 10 rows shown):</p>
<center><img src="https://res.cloudinary.com/dyd911kmh/image/upload/v1643102294/image11_agylng.png" alt="GROUP BY &amp; COUNT( )"></center>

<h3 id="sum-">SUM( )</h3>
<p>How about how much money each customer has spent? We can accomplish this using the SUM aggregator function. Here, we want the top spender on top.</p>
<pre><code class="lang-SQL hljs">SELECT customer_id, SUM(amount)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC
</code></pre><p>Which returns (only the first 10 rows shown):</p>
<center><img src="https://res.cloudinary.com/dyd911kmh/image/upload/v1643102295/image17_nar8bk.png" alt="SUM( )"></center>

<h3 id="avg-having">AVG( ) &amp; HAVING</h3>
<p>We can add additional conditions using HAVING after you have calculated a grouped aggregation. For example, we want to know the MAAP rating categories that have movies that are longer than 115 minutes on average?</p>
<pre><code class="lang-SQL hljs">SELECT rating, AVG(length)
FROM film
GROUP BY rating
HAVING AVG(length) &gt; 115
</code></pre><p>Which returns:</p>
<center><img src="https://res.cloudinary.com/dyd911kmh/image/upload/v1643102293/image5_wzlvp2.png" alt="AVG( ) &amp; HAVING"></center>

<h3 id="min-alias">MIN( ) &amp; Alias</h3>
<p>Grouping is not always necessary using an aggregation. If you want to know the shortest movie length, we can simply use the MIN function. We also want the column to read as 'shortest_movie_length', which can be accomplished by assigning an alias using 'AS'.</p>
<pre><code class="lang-SQL hljs">SELECT MIN(length) AS shortest_movie_length
FROM film
</code></pre><p>Which returns (only the first 10 rows shown):</p>
<center><img src="https://res.cloudinary.com/dyd911kmh/image/upload/v1643102293/image3_kvqpfb.png" alt="MIN( ) &amp; Alias"></center>

<p>You can also use an alias for table names. This is useful when you are working with long table names, especially when joining tables (see below). We also want to be mindful of what kind of alias we use as it can drastically change the readability of the code .</p>
<p>There are other aggregation functions that are useful, and you can learn more about them in the <a target="_blank" href="https://www.datacamp.com/courses/introduction-to-sql">Introduction to SQL course</a>.</p>
<h2 id="joins">Joins</h2>
<h3 id="inner-join-join-">INNER JOIN (JOIN)</h3>
<p>You have probably noticed that there are only limited things we can do by looking at a single table. We often want to look at the data from multiple tables. This is where we use JOINs. We can join tables based on a common key. For example, if we were looking for films that are not in English:</p>
<pre><code class="lang-SQL hljs">SELECT film.title, language.name
FROM film
JOIN language
    ON film.language_id = language.language_id
WHERE language.name != &#39;English&#39;
</code></pre><p>(Please note that there are no movies other than those in English in this database. You will get an empty table with this query right above.)</p>
<p>You can also join multiple tables. For example, below we want to know the categories and their corresponding average rating. We've also returned values ordered by the average rating, with the highest first.</p>
<pre><code class="lang-SQL hljs">SELECT category.name, AVG(film.rental_rate) AS average_rating
FROM film
JOIN film_category
    ON film.film_id = film_category.film_id
JOIN category
    ON film_category.category_id = category.category_id
GROUP BY category.name
ORDER BY average_rating DESC
</code></pre><p>Which returns (only the first 10 rows shown):</p>
<center><img src="https://res.cloudinary.com/dyd911kmh/image/upload/v1643102296/image6_eemfze.png" alt="INNER JOIN (JOIN)"></center>

<p>There are many types of joins: INNER JOIN, LEFT JOIN, RIGHT JOIN, OUTER JOIN, CROSS JOIN. Here, we used JOIN, which by default is an INNER JOIN in which you get back entries only if they are present in both tables. If you would like to learn more about the differences between these joins, you can take a look at the <a target="_blank" href="https://www.datacamp.com/courses/joining-data-in-sql">Joining Data in SQL</a> course.</p>
<h2 id="changing-data-types">Changing Data Types</h2>
<h3 id="cast-">CAST( )</h3>
<p>In the example used above where we calculated dollar amounts, you might have noticed that SQL only sees the values as numbers. We can change these to dollar amounts using the CAST function:</p>
<pre><code class="lang-SQL hljs">SELECT customer_id, CAST(SUM(amount) AS money)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC
</code></pre><p>Which returns (only the first 10 rows shown):</p>
<center><img src="https://res.cloudinary.com/dyd911kmh/image/upload/v1643102293/image2_p13a5u.png" alt="CAST( )"></center>

<p>Money is not the only thing that we can change values into. We can change numbers into floats, text, and even into date and time.</p>
<h3 id="round-">ROUND( )</h3>
<p>Similarly, we can also round numbers. In the example above in which we calculated the average rental rating, there are many decimal points in the calculated values. We can use the ROUND function to round to two decimals:</p>
<pre><code class="lang-SQL hljs">SELECT category.name, ROUND(AVG(film.rental_rate), 2) AS average_rating
FROM film
JOIN film_category
    ON film.film_id = film_category.film_id
JOIN category
    ON film_category.category_id = category.category_id
GROUP BY category.name
ORDER BY average_rating DESC
</code></pre><p>Which returns (only the first 10 rows shown):</p>
<center><img src="https://res.cloudinary.com/dyd911kmh/image/upload/v1643102296/image14_jsv1oa.png"></center>

<h2 id="complex-conditions">Complex Conditions</h2>
<h3 id="case-statement">CASE Statement</h3>
<p>You can imagine that by combining many of the above functions and clauses, you can perform a fairly complex query. However, there are situations in which the basics are not sufficient. For example, if we wanted to add a column with movie-length categories. We can label movies as &#39;Short Film&#39; if less than 50 minutes, &#39;Long Film&#39; if more than 150 minutes, and &#39;Medium Film&#39; for the rest. We can accomplish this by using a CASE statement:</p>
<pre><code class="lang-SQL hljs">SELECT title, length,
CASE
    WHEN length &lt; 50 THEN &#39;Short Film&#39;
    WHEN length &gt; 150 THEN &#39;Long Film&#39;
    ELSE &#39;Medium Film&#39;
END AS Movie_Length_Category
FROM film
</code></pre><p>Which returns (only the first 10 rows shown):</p>
<center><img src="https://res.cloudinary.com/dyd911kmh/image/upload/v1643102295/image16_hkiwbn.png" alt="CASE Statement"></center>

<p>As you can see, the result is returned when the specified condition is met. This is similar to 'if-else' statements in programming languages.</p>
<h3 id="subqueries">Subqueries</h3>
<p>We may now want to know which movies are the longest in length, and their associated titles. For this, we can use the WHERE clause that we introduced above, just with a more complex condition:</p>
<pre><code class="lang-SQL hljs">SELECT title, length
FROM film
WHERE length = (SELECT max(length)
                FROM film)
</code></pre><p>Which returns:</p>
<center><img src="https://res.cloudinary.com/dyd911kmh/image/upload/v1643102295/image15_vuxcbi.png" alt="Subqueries"></center>

<p>Complex queries can happen using subqueries. In this example, we want to know the list of movie titles that have a higher rental rating than the average rental rating of all comedy films:</p>
<pre><code class="lang-SQL hljs">SELECT title, rental_rate
FROM film
WHERE rental_rate &gt;
(SELECT AVG(film.rental_rate) AS Average_Rating
FROM film
JOIN film_category AS cat_id
    ON film.film_id=cat_id.film_id
JOIN category AS cat
    ON cat_id.category_id=cat.category_id
GROUP BY cat.name, cat_id.category_id
HAVING cat.name = &#39;Comedy&#39;)
</code></pre><p>Which returns (only the first 10 rows shown):</p>
<center><img src="https://res.cloudinary.com/dyd911kmh/image/upload/v1643102294/image7_fvoyrh.png" alt="Which returns (only the first 10 rows shown):"></center>

<p>In this example, we first find the average film rental rating for comedy films. The value found in this query (seen inside the parentheses above) is then used as a condition in the WHERE clause in which we can find the movie titles.</p>
<h3 id="common-table-expressions-ctes-">Common Table Expressions (CTEs)</h3>
<p>In a more complex example, we can use common table expression (CTE) to save a temporary table from which we extract the information needed. For this example, we want to know the average number of rentals each staff processes per week.</p>
<pre><code class="lang-SQL hljs">WITH weekly_rentals AS(
    SELECT
        staff_id,
        DATE_PART(&#39;week&#39;, payment_date) AS week,
        COUNT(rental_id) AS rental_numbers
    FROM payment
    GROUP BY staff_id, week)

SELECT staff_id, AVG(rental_numbers)
FROM weekly_rentals
GROUP BY staff_id
</code></pre><p>Which returns:</p>
<center><img src="https://res.cloudinary.com/dyd911kmh/image/upload/v1643102295/image18_d8ruge.png" alt="Common Table Expressions (CTEs)"></center>

<p>In the above case, we are saving the number of rentals per week in a temporary table called &#39;weekly_rentals&#39; upon which we can then perform another query to get the final result.</p>
<h3 id="window-functions">Window Functions</h3>
<p>Window functions are another set of SQL skills that are important and can take you to the next level. Even though window functions are considered intermediate to advanced level skills, many data analysts and scientists believe them to be essential for their jobs. It is therefore worthwhile for you to spend some time getting familiar with them.</p>
<p>OVER( ) &amp; PARTITION BY
Here, let's say we want to find out each staff&#39;s weekly number of rentals and the overall total number of rentals, along with the staff ID and the week number. We can reuse the CTE generated in the above example, as shown below:</p>
<pre><code class="lang-SQL hljs">WITH weekly_rentals AS(
    SELECT
        staff_id,
        DATE_PART(&#39;week&#39;, payment_date) AS week,
        COUNT(rental_id) AS rental_numbers
    FROM payment
    GROUP BY staff_id, week)

SELECT *, SUM(rental_numbers) OVER() AS total_rentals
FROM weekly_rentals
</code></pre><p>Which returns (only the first 10 rows shown):</p>
<center><img src="https://res.cloudinary.com/dyd911kmh/image/upload/v1643102293/image1_zizhng.png" alt="Window Functions"></center>

<p>Having OVER in the code specifies that it's a window function. Here, we are saying the window function (SUM here) is applied to every row. We can also use the window function along with PARTITION BY to apply the window function at specified 'partitions'. Let&#39;s say that we now want to figure out the total number of rentals per week instead of the overall number of rentals (in addition to the other columns. Again, in this example below we are using the CTE generated <a target="_blank" href="https://docs.google.com/document/d/1J1UeJEvyURNO-NDlekfvmbSpoGyb9dDL_SyeWt25PXo/edit#heading=h.qr6vhyayaxfv">before</a>.</p>
<pre><code class="lang-SQL hljs">WITH weekly_rentals AS(
    SELECT
        staff_id,
        DATE_PART(&#39;week&#39;, payment_date) AS week,
        COUNT(rental_id) AS rental_numbers
    FROM payment
    GROUP BY staff_id, week)

SELECT *, SUM(rental_numbers) OVER(PARTITION BY week) AS total_weekly_rentals
FROM weekly_rentals
</code></pre><p>Which returns (only the first 10 rows shown):</p>
<center><img src="https://res.cloudinary.com/dyd911kmh/image/upload/v1643102294/image13_dujtb3.png" alt="Window Functions 2"></center>

<p>The additional column contains the total weekly rental numbers instead of the running total.</p>
<p>We introduced only a couple of simple examples of window functions here that are important for being a data analyst or scientist in many companies. We encourage you to learn more about these commands using the links provided. You could also check out the <a target="_blank" href="https://www.datacamp.com/courses/intermediate-sql">Intermediate SQL course</a> to learn or brush up your SQL skills. Additionally, you can practice your skills by taking an open source database and trialling these different commands while thinking about relevant questions to answer.</p>


https://www.datacamp.com/community/tutorials/tutorial-sql-commands-for-data-scientists

Learn the basic and essential commands for SQL with examples and a workspace notebook to refer to. 

Find detailed Basic SQL commands for handling data across SQL platforms. Learn to use SELECT, AVG,SUM,GROUPBY, etc. to explore data. 

community

datacamp

sql

News (7)

Creating Smart Cities With Data Science

SQL Tutorial: How To Write Better Queries

Creating Smart Cities With Data Science

Tutorials (46)

How to Execute SQL Queries in Python and R Tutorial

SQL Commands for Data Scientists

Cheat Sheets (0)

Open Courses (1)

SQL Tutorial for Marketers

Podcast (21)

Successful Digital Transformation Puts People First (with Bhavin Patel)

Blog Posts (57)

How Data & Culture Unlock Digital Transformation

Tech Thoughts (0)