fbpx
Home Blog Page 1315

Ask a Data Engineer: Warby Parker Edition 👓

By
onlinefreecourse
-
0
Ask a Data Engineer: Warby Parker Edition 👓

wp-header

Codecademy’s very own Nick Duckwiler (left) and Ryan Tuck from Warby Parker (right) in our office. (📷: Mitch Boyer)

Last month, Codecademy and Warby Parker came together to work on a special Learn SQL from Scratch Capstone Project. It was during this time when I met Ryan Tuck, a Data Engineer at Warby, who played a major part in this partnership. So when he decided to drop by our office for the final QA round, I had to break out my notebook and ask some questions. Enjoy.

Hey Ryan, let’s start off with a question I’ve had for a while — what is a Data Engineer? (Is it similar to a Data Analyst or a Software Engineer?)

At Warby Parker, data engineers are responsible for creating and maintaining the plumbing required to support the data and reporting needs of the business. We use software engineering practices to automate the work of data cleaning, normalizing, and model building so that data is always ready to be consumed by data analysts in every department.

What languages/frameworks do you use at Warby?

On data engineering, we use Python as our general purpose programming language, as do most of the other teams in our Technology department. When it comes to databases, we use PostgreSQL for the majority of our SQL needs, and are beginning to use Amazon Athena and Google BigQuery for some of our larger datasets. We use Looker as our exclusive business intelligence entry point to all of this data.

What are some of the projects you worked on?

I’ve had the privilege of working with a lot of of smart people in every department at our company to help them solve their varied data needs, from reconciling financial data with the Accounting team to automating and modeling standardized performance metrics for our team of over 200 customer experience advisors.

As part of a team of five supporting the data needs of a rapidly growing company, I’ve tried where possible to focus on helping our analysts solve their own problems. This includes helping people learn Python and commit to our codebase, guiding the creation of data models in SQL, and encouraging people to submit pull requests to add features in Looker, our BI tool.

Seeing dozens of otherwise “non-technical” colleagues opening up PRs on a daily basis, and consequently being part of the democratization of tech that we value at Warby Parker, is probably the most rewarding “project” I’ve been a part of.

One project finished recently during our first annual “Hackweek” is called Pipes, which allows anyone at the company to easily move large amounts of data from wherever to wherever (Looker, Google Sheets, PostgreSQL, BigQuery, etc) on a regular cadence, or manually through a simple one-line chatbot interface. The adoption has been overwhelmingly positive and we’re looking to grow this sort of tooling out even more.

“We use software engineering practices to automate the work of data cleaning, normalizing, and model building so that data is always ready to be consumed by data analysts in every department.”

What got you into the data field?

I’ve always been drawn to analytical fields like math, and became pretty proficient in Excel during some internships in college. Once I had learned to program and learned more about data science and its applications in artificial intelligence, I knew that anything I could do to immerse myself in the world of data would be a step in the right direction.

Three and a half years ago, I landed a job as a junior software engineer at Warby Parker not fully knowing what I was in for, but am so glad I got the opportunity to help build tools to support an interesting and ever-changing data-driven culture here.

Where did you learn SQL and Python?

I had a background in C++, and was exposed to Python through an Intro to Data Science course. When Warby Parker hired me onto the Data team in 2015, I had never written a SQL query in my life, but picked it up quickly and within a few months started up internal SQL training classes, which I still teach on a monthly basis.

What does your tattoo say?


The ultimate cheatsheet.

This is Bayes’ Theorem, which is an equation that describes how to update probabilities given new evidence. Two summers ago I worked on building a tool to help predict weekly fantasy football performance. Some colleagues suggested a Bayesian approach would be appropriate, since there aren’t really enough data points in an NFL season to be able to use statistical approaches that require larger datasets, and I’d want to regularly update my predictions after each player’s latest performance.

I did a deep dive into understanding the (simple) math underlying Bayes’ Theorem and came out of that experience with a whole new worldview, understanding my entire knowledge of the world as a big and intricate probabilistic model that I was continuously updating with every experience I ever have. It was pretty transformative, and I figured that was worth a tattoo.

What is a concept in SQL/Python that’s essential to your work?

Donald Knuth said, “Premature optimization is the root of all evil.” I’ve generally found this to be true, and try to live by it in my work. For example, I’ll generally prefer to keep a data model simple by rebuilding it for all time on a daily basis using a single SQL query instead of making a more complicated model that requires iteratively adding to a table, keeping track of state, updated timestamps, when something last ran, etc.

A wise man once said, “Duplicating data makes things go fast,” but databases are already impressively fast to begin with, without implementing anything to improve performance. Ultimately, I almost always approach a problem thinking about optimizing for my time over machine time, for readability over performance, and for introducing as little cognitive overhead as is required by the problem at hand. Only once performance issues or readability issues present themselves will some code be worth a rewrite.

Last question! Since you wrote Warby Parker’s internal SQL training courses, I know there gotta be some inner Curriculum Developer in you. Can you teach a SQL concept in 2 minutes?

Sure! Have you ever written a query that yields some result set and you think, “I’d love to query the stuff I just produced like it was a table?” Enter the WITH clause.

Suppose I have a mega query that gives the transaction summaries:

select
    transactions.date as transaction_date,
    sum(items.price) as total_cost,
    count(*) as number_of_items
from
    transactions
inner join
    customers
    on
    customers.id = transactions.customer_id
inner join
    transaction_items
    on
    transactions.id = transaction_items.transaction_id
inner join
    items
    on
    items.id = transaction_items.item_id

Using WITH, I can create a temporary table within my query that I can SELECT from and treat it just like a regular old table.

I will put everything from the previous query in a parentheses and use WITH to give it the name transaction_summaries.

Then I’ll apply the date and customer filtering down below for a more readable query, to separate out all the JOIN logic from the actual WHERE filters that I want to apply on that data.

with transaction_summaries as (
  select
      transactions.date as transaction_date,
      sum(items.price) as total_cost,
      count(*) as number_of_items
  from
      transactions
  inner join
      customers
      on
      customers.id = transactions.customer_id
  inner join
      transaction_items
      on
      transactions.id = transaction_items.transaction_id
  inner join
      items
      on
      items.id = transaction_items.item_id
)

select 
        * 
from 
        transaction_summaries
where 
        first_name = 'beyonce'
        and 
        transaction_date > '2018–01–01'
order by 
        total_cost desc
limit 
        5

If you’re familiar with subqueries, this does a similar thing but makes the SQL far more readable, even if your query isn’t quite as performant as it would have been. This is essentially an implementation of the mantra “Don’t Repeat Yourself” that’s common in the world of programming.

Incredible. And love the SQL styling! 😍

Huge shout out to Ryan and the whole Warby Parker team for making this partnership happen. Special hat tips for behind-the-scenes support from:

  • Lon Binder, Chief Technology Officer, Warby Parker
  • Maddie Tierney, Executive Assistant, Warby Parker
  • Kayla Robbins, Executive Assistant, Warby Parker
  • Kaki Read, Senior Communications Manager, Warby Parker
  • Isabel Seely, Senior Brand Manager, Warby Parker

It’s been an absolute pleasure. And of course, the fam at Codecademy. You know who you are. Couldn’t do it without you.

GUEST POST: Self-Referencing as a Tool to Improve Learning

By
onlinefreecourse
-
0
GUEST POST: Self-Referencing as a Tool to Improve Learning

References:

(1) Symons, C. S., & Johnson, B. T. (1997). The self-reference effect in memory: A meta-analysis. Psychological Bulletin, 121, 371-394

(2) Humphreys, G. W., & Siu, J. (2016). Attentional control and the self: The self-attention network (SAN). Cognitive Neuroscience, 7, 5-17..

(3) Turk, D. J., Gillespie-Smith, K., Krigolson, O. E., Havard, C., Conway, M. A., & Cunningham, S. J. (2015) Selfish learning: The impact of self-referential encoding on children’s literacy attainment, Learning and Instruction, 40, 54-60.

(4) Cunningham, S. J. Turk, D. J., Macdonald, L. M., & Macrae, C. N. (2008). Yours or mine? Ownership and memory. Consciousness and Cognition, 17, 312-318.

(5) Cunningham, S. J., Scott, L., Hutchison, J., Ross, J. & Martin, D. (2018). Applying self-processing biases in education: Improving learning through ownership. Journal of Applied Research in Memory and Cognition, 7, 342-351.

Do You Push Your Parents’ Buttons?

By
onlinefreecourse
-
0
Do You Push Your Parents’ Buttons?

Are there things you do that drive your parents crazy?

Is it not cleaning your room? Leaving half-eaten food around the house? Spending too much time on the phone? Playing your music too loud? Mumbling your answers? Not saying thank you?

How do your parents handle these situations? Do their responses ever lead to positive changes in your behavior?

Do you ever wish they tried a different approach to these moments?

In “How to Stop Thinking Your Teen Is ‘Pushing Your Buttons’,” Cheryl Maguire writes:

My 14-year-old daughter constantly abandons her coat on the floor and leaves half-eaten food in the living room and crumpled papers in the hall. I end up cleaning up after her, which I’ve repeatedly told her makes me upset.

She’s a smart, talented kid. So why does she keep pushing my buttons?

At some point most parents feel as if their teenager is acting in ways to intentionally make them angry. But experts say that the interaction is often more about the way the parent responds than about the teenager’s behavior.

The author provides advice to parents to better handle situations when they feel their buttons are being pushed by their teenagers. Here are excerpts from three:

Change the Language

“When a parent tells me their kid is ‘pushing their buttons,’ I let them know we need to change the language,” said Dr. Alexandra Solomon, a Northwestern University professor, clinical psychologist and author of “Loving Bravely.”

She said that thinking your child is controlling you is disempowering, which can lead to a battle of wills.

Such conflicts often fall into one of three categories, Dr. Solomon said.

The first is when the parents are thinking about their own teen behavior. Parents may project their fears, memories and challenges onto the relationship and can’t see their child as separate from themselves. Dr. Solomon gave an example: “The boy I dated when I was 16 cheated on me and broke my heart. My daughter should not date because all high school boys are immature and irresponsible.”

Another involves thinking of past mistakes they made as parents. Dr. Solomon said that if a teenager has trouble making friends a parent may think, “If I had taken my child on play dates when they were younger then they would have friends now.”

The last type is when a parent “fast forwards” to possible future behaviors. This is when a parent thinks, “If my kid is doing this at age 13 then what are they going to be doing at age 16?”

All of these patterns involve being ruled by fear instead of guided by love, Dr. Solomon said. Fear-driven parents often become controlling, creating strict rules, grounding their children or infringing on their privacy. “When these rules are created from a fear-based mind-set instead of what is necessary based on your teen’s developmental needs, an unhealthy relationship will develop,” Dr. Solomon said.

Be on the Same Team

Say to your teenager: “This isn’t working for either of us. What can we do to fix it?” Maybe the coat closet is near the front door and your kids don’t use it because they come in through the back door. Could you install a coat hook near the back door?

Once you have a plan, even if there is only a small improvement, praise your child for doing a good job and acknowledge that you have a better relationship because you are working together.

“Getting mad at your child isn’t going to change the behavior,” said Dr. Carla Naumburg, a clinical social worker and author of parenting books. “When you get angry, your attention is on the conflict instead of figuring out a solution to the problem.”

Trouble Shoot

If you have setbacks, ask your child, “Where do you think we went wrong?”

“Approach situations with curiosity. If your son doesn’t get out of bed, don’t say, ‘You are tired because you stayed up too late.’” Dr. Solomon said. Instead ask, ‘Why do you think you are tired?’ Hold back the urge to be right and instead stay curious by asking questions.

If you do yell at your teen, Dr. Naumburg recommended apologizing. “Some parents worry that apologizing will undermine their authority, but that isn’t true,” she said. “It’s a respectful way to be in a relationship and it’s modeling a behavior that we want our kids to do — take responsibility for their actions.”

Students, read the entire article, then tell us:

— Do you ever push your parents’ buttons? Tell us about a recent example. Why do you think your behavior got under your parents’ skin? How did they handle the moment? Did it help the situation or make it worse? How did you all feel later?

— The article provides several reasons parents feel aggravated by their teenager’s behavior. Do any of these explanations seem to fit your parents? What insights into parenting does the article provide?

— What do you wish your parents would understand about your behavior? Are there things you think you could do to make your parents’ lives a little easier and less stressful?

— What do you think about the author’s advice to parents? Which of the techniques that she recommends would you like to see your own parents try, and why? Are there any other strategies a parent has used that have worked?

— What advice would you give your parents? What advice would you give yourself if you become a parent in the future?

Students 13 and older are invited to comment. All comments are moderated by the Learning Network staff, but please keep in mind that once your comment is accepted, it will be made public.

Learning With: ‘In San Francisco, Making a Living From Your Billionaire Neighbor’s Trash’

By
onlinefreecourse
-
0
Learning With: ‘In San Francisco, Making a Living From Your Billionaire Neighbor’s Trash’

Before reading the article:

Do you ever think about how much you and your family throw away?

What’s in your trash? Could other people still use it? Do you ever feel guilty about tossing it out? Do you ever give away, or donate things, you don’t need?

Americans throw away hundreds of millions of tons of trash each year. The average person produces nearly five pounds of trash each day, while a family creates about 17.4 pounds of trash daily, which adds up to roughly 1,600 pounds per person and 6,000 pounds per family each year.

If you piled all the trash Americans produce in a year, it would reach the moon and back 25 times!

Jake Orta, 56, an Air Force veteran, is one of hundreds of people in San Francisco who earn their living from the garbage other people have thrown out.

First, look at the photographs featured in the article. What can you tell about the life of a full-time trash picker in San Francisco? What story do these photographs tell?

Now, read the article, “In San Francisco, Making a Living From Your Billionaire Neighbor’s Trash,” and answer the following questions:

1. The article begins, “Three blocks from Mark Zuckerberg’s $10 million Tudor home in San Francisco, Jake Orta lives in a small, single-window studio apartment filled with trash.” Why did Thomas Fuller, the author, start with this comparison? What details does he provide in the next two paragraphs to further describe the setting? What kind of picture do they paint?

2. The author writes that “trash scavengers exist in many United States cities and, like the rampant homelessness in San Francisco, are a signpost of the extremes of American capitalism.” What does this statement mean? How does he support this claim?

3. How much does Mr. Orta earn a day from the trash he finds? Describe the circuit he travels each day to find trash. What rules does he follow?

4. Which biographical details about Mr. Orta did you find most interesting? Why has he struggled with homelessness through his adult life?

5. Why does Robert Reed, the spokesman for Recology, the company that collects garbage in San Francisco, say much of the waste in his city is the “trash of convenience”? How is this beneficial to Mr. Orta’s search for valuable garbage?

6. How does Mr. Orta compare to other trash pickers in San Francisco? Why does he see himself as more of a “treasure hunter” than a trash picker?

7. What are some of the most notable items Mr. Orta has found in other people’s garbage. Which does he consider his favorite? Which is the most intriguing to you?

Finally, tell us more about what you think:

— What is your reaction to Mr. Orta’s journey from Air Force member to homeless person to treasure hunter?

— Nick Marzano, an Australian photographer who documents the world of trash pickers in San Francisco, calls the occupation of trash pickers like Mr. Orta “a civic service.” Do you agree?

— What does it say about our society that some live off the items that are discarded by others? Do you agree with the author that it is a “signpost of the extremes of American capitalism”? Do you disagree? Why?

— Does the article make you rethink what and how much you throw away? If yes, how has it changed how you might now handle the items you would normally consider garbage?

— What’s the coolest thing you ever found in the trash? Did you ever wonder how or why it got there? Do you agree with the saying “one person’s trash is another person’s treasure”?

— In 1962, John Steinbeck wrote:

American cities are like badger holes, ringed with trash — all of them — surrounded by piles of wrecked and rusting automobiles, and almost smothered with rubbish. Everything we use comes in boxes, cartons, bins, the so-called packaging we love so much. The mountains of things we throw away are much greater than the things we use. In this, if no other way, we can see the wild and reckless exuberance of our production, and waste seems to be the index.

What do you think of his perspective on American waste? Is it still true? What, if anything, has changed? What does what we throw away say about our society?

‘The Image of the Revolution’

By
onlinefreecourse
-
0
‘The Image of the Revolution’

In a recent article, Vanessa Friedman writes:

Every once in a while an image appears that so viscerally frames the human story in a time of social or political paroxysm that it becomes a symbol. Such was the case this week with a smartphone photo taken during a demonstration in Sudan against the repressive regime of President Omar al-Bashir, as the protests that have been going on intermittently since December reached a new intensity.

What is it about this picture that makes it so powerful? Why do you think it’s being called “the image of the revolution”?

What other photographs can you recall from recent history that have had this same kind of symbolic power? What makes them stand out to you?

Tell us in the comments, then read the related article to find out more about the force behind this photo.

Find many more ways to use our Picture Prompt feature in this lesson plan.

Word + Quiz: gratis

By
onlinefreecourse
-
0
Word + Quiz: <strong></strong>gratis

adjective: costing nothing

adverb: without payment

_________

The word gratis has appeared in 12 articles on NYTimes.com in the past year, including on Nov. 23 in “Send in the Stagers” by Joanne Kaufman:

When the market was booming, even an estate sale might call for only minimal tweaking. “If an apartment was in nice condition with a view, you expected prospective buyers to come in and have their own imagination and know how a room could be laid out,” Mr. Moss continued. “Now every apartment has to be prepared for showing.”

… In some cases, real estate agents themselves will take on the role of ad hoc stager, in the interest of saving a client both the cost of furniture rental and the staging fees charged by the professionals.

Ms. Beauchamp, of Engel & Volkers, has a cache of chairs, lamps, glassware, vases and multiple sets of bed linens that she keeps in her own apartment and deploys gratis to dress up residences that she finds wanting. “It tells clients ‘I’m in this with you,’” she said.

_________

Film Club: ‘The Animated Life of A.R. Wallace’

By
onlinefreecourse
-
0
Film Club: ‘The Animated Life of A.R. Wallace’

Why does history remember some and not others?

“The Animated Life of A.R. Wallace” is an eight-minute film that touches on themes of struggle, persistence and recognition. It profiles the life and legacy of scientist Alfred Russel Wallace who is co-credited with Charles Darwin for the theory of natural selection, but has been all but forgotten by history. Through paper-puppet animation, the film details the many obstacles and triumphs Wallace faced on his way to coming up with the theory of evolution.

Students

1. Watch the short film above. While you watch, you might take notes using our Film Club Double-Entry Journal (PDF) to help you remember specific moments.

2. After watching, think about these questions:

• What moments in this film stood out for you? Why?

• Were there any surprises? Anything that challenged what you know — or thought you knew?

• What messages, emotions or ideas will you take away from this film? Why?

• What questions do you still have?

3. An additional challenge: What connections can you make between this film and your own life or experience? Why? Does this film remind you of anything else you’ve read or seen? If so, how and why?

4. Next, join the conversation by clicking on the comment button and posting in the box that opens on the right. (Students 13 and older are invited to comment, although teachers of younger students are welcome to post what their students have to say.)

5. After you have posted, try reading back to see what others have said, then respond to someone else by posting another comment. Use the “Reply” button or the @ symbol to address that student directly.

6. To learn more, read “The Animated Life of A.R. Wallace.” Flora Lichtman and Sharon Shattuck write:

Although Alfred Russel Wallace made one of the most important scientific discoveries in history, he’s been all but forgotten. A contemporary of Charles Darwin, Wallace was the other guy to discover natural selection — the evolutionary process whereby better adapted organisms are more likely to survive and pass on their traits than less adapted ones. Although two people discovered this theory, evolution by natural selection is virtually synonymous with Darwin. This is partly due to the lasting fame of Darwin’s opus, “On the Origin of Species,” but some argue it is also due to Wallace’s extraordinary modesty — he lauded Darwin’s work and humbly downplayed his own contributions. In 1889 he even wrote a book in support of evolution titled “Darwinism.”

_________

More?

• See all the films in this series.

• Read our list of practical teaching ideas, along with responses from students and teachers, for how you can use these documentaries in the classroom.

• Our next Film Club will take place on Thursday, April 18.

Ask a Data Engineer: Warby Parker Edition 👓

By
onlinefreecourse
-
0
Ask a Data Engineer: Warby Parker Edition 👓

wp-header

Codecademy’s very own Nick Duckwiler (left) and Ryan Tuck from Warby Parker (right) in our office. (📷: Mitch Boyer)

Last month, Codecademy and Warby Parker came together to work on a special Learn SQL from Scratch Capstone Project. It was during this time when I met Ryan Tuck, a Data Engineer at Warby, who played a major part in this partnership. So when he decided to drop by our office for the final QA round, I had to break out my notebook and ask some questions. Enjoy.

Hey Ryan, let’s start off with a question I’ve had for a while — what is a Data Engineer? (Is it similar to a Data Analyst or a Software Engineer?)

At Warby Parker, data engineers are responsible for creating and maintaining the plumbing required to support the data and reporting needs of the business. We use software engineering practices to automate the work of data cleaning, normalizing, and model building so that data is always ready to be consumed by data analysts in every department.

What languages/frameworks do you use at Warby?

On data engineering, we use Python as our general purpose programming language, as do most of the other teams in our Technology department. When it comes to databases, we use PostgreSQL for the majority of our SQL needs, and are beginning to use Amazon Athena and Google BigQuery for some of our larger datasets. We use Looker as our exclusive business intelligence entry point to all of this data.

What are some of the projects you worked on?

I’ve had the privilege of working with a lot of of smart people in every department at our company to help them solve their varied data needs, from reconciling financial data with the Accounting team to automating and modeling standardized performance metrics for our team of over 200 customer experience advisors.

As part of a team of five supporting the data needs of a rapidly growing company, I’ve tried where possible to focus on helping our analysts solve their own problems. This includes helping people learn Python and commit to our codebase, guiding the creation of data models in SQL, and encouraging people to submit pull requests to add features in Looker, our BI tool.

Seeing dozens of otherwise “non-technical” colleagues opening up PRs on a daily basis, and consequently being part of the democratization of tech that we value at Warby Parker, is probably the most rewarding “project” I’ve been a part of.

One project finished recently during our first annual “Hackweek” is called Pipes, which allows anyone at the company to easily move large amounts of data from wherever to wherever (Looker, Google Sheets, PostgreSQL, BigQuery, etc) on a regular cadence, or manually through a simple one-line chatbot interface. The adoption has been overwhelmingly positive and we’re looking to grow this sort of tooling out even more.

“We use software engineering practices to automate the work of data cleaning, normalizing, and model building so that data is always ready to be consumed by data analysts in every department.”

What got you into the data field?

I’ve always been drawn to analytical fields like math, and became pretty proficient in Excel during some internships in college. Once I had learned to program and learned more about data science and its applications in artificial intelligence, I knew that anything I could do to immerse myself in the world of data would be a step in the right direction.

Three and a half years ago, I landed a job as a junior software engineer at Warby Parker not fully knowing what I was in for, but am so glad I got the opportunity to help build tools to support an interesting and ever-changing data-driven culture here.

Where did you learn SQL and Python?

I had a background in C++, and was exposed to Python through an Intro to Data Science course. When Warby Parker hired me onto the Data team in 2015, I had never written a SQL query in my life, but picked it up quickly and within a few months started up internal SQL training classes, which I still teach on a monthly basis.

What does your tattoo say?


The ultimate cheatsheet.

This is Bayes’ Theorem, which is an equation that describes how to update probabilities given new evidence. Two summers ago I worked on building a tool to help predict weekly fantasy football performance. Some colleagues suggested a Bayesian approach would be appropriate, since there aren’t really enough data points in an NFL season to be able to use statistical approaches that require larger datasets, and I’d want to regularly update my predictions after each player’s latest performance.

I did a deep dive into understanding the (simple) math underlying Bayes’ Theorem and came out of that experience with a whole new worldview, understanding my entire knowledge of the world as a big and intricate probabilistic model that I was continuously updating with every experience I ever have. It was pretty transformative, and I figured that was worth a tattoo.

What is a concept in SQL/Python that’s essential to your work?

Donald Knuth said, “Premature optimization is the root of all evil.” I’ve generally found this to be true, and try to live by it in my work. For example, I’ll generally prefer to keep a data model simple by rebuilding it for all time on a daily basis using a single SQL query instead of making a more complicated model that requires iteratively adding to a table, keeping track of state, updated timestamps, when something last ran, etc.

A wise man once said, “Duplicating data makes things go fast,” but databases are already impressively fast to begin with, without implementing anything to improve performance. Ultimately, I almost always approach a problem thinking about optimizing for my time over machine time, for readability over performance, and for introducing as little cognitive overhead as is required by the problem at hand. Only once performance issues or readability issues present themselves will some code be worth a rewrite.

Last question! Since you wrote Warby Parker’s internal SQL training courses, I know there gotta be some inner Curriculum Developer in you. Can you teach a SQL concept in 2 minutes?

Sure! Have you ever written a query that yields some result set and you think, “I’d love to query the stuff I just produced like it was a table?” Enter the WITH clause.

Suppose I have a mega query that gives the transaction summaries:

select
    transactions.date as transaction_date,
    sum(items.price) as total_cost,
    count(*) as number_of_items
from
    transactions
inner join
    customers
    on
    customers.id = transactions.customer_id
inner join
    transaction_items
    on
    transactions.id = transaction_items.transaction_id
inner join
    items
    on
    items.id = transaction_items.item_id

Using WITH, I can create a temporary table within my query that I can SELECT from and treat it just like a regular old table.

I will put everything from the previous query in a parentheses and use WITH to give it the name transaction_summaries.

Then I’ll apply the date and customer filtering down below for a more readable query, to separate out all the JOIN logic from the actual WHERE filters that I want to apply on that data.

with transaction_summaries as (
  select
      transactions.date as transaction_date,
      sum(items.price) as total_cost,
      count(*) as number_of_items
  from
      transactions
  inner join
      customers
      on
      customers.id = transactions.customer_id
  inner join
      transaction_items
      on
      transactions.id = transaction_items.transaction_id
  inner join
      items
      on
      items.id = transaction_items.item_id
)

select 
        * 
from 
        transaction_summaries
where 
        first_name = 'beyonce'
        and 
        transaction_date > '2018–01–01'
order by 
        total_cost desc
limit 
        5

If you’re familiar with subqueries, this does a similar thing but makes the SQL far more readable, even if your query isn’t quite as performant as it would have been. This is essentially an implementation of the mantra “Don’t Repeat Yourself” that’s common in the world of programming.

Incredible. And love the SQL styling! 😍

Huge shout out to Ryan and the whole Warby Parker team for making this partnership happen. Special hat tips for behind-the-scenes support from:

  • Lon Binder, Chief Technology Officer, Warby Parker
  • Maddie Tierney, Executive Assistant, Warby Parker
  • Kayla Robbins, Executive Assistant, Warby Parker
  • Kaki Read, Senior Communications Manager, Warby Parker
  • Isabel Seely, Senior Brand Manager, Warby Parker

It’s been an absolute pleasure. And of course, the fam at Codecademy. You know who you are. Couldn’t do it without you.

Easter Gift Tags | Free Printable

By
onlinefreecourse
-
0
Easter Gift Tags | Free Printable

If you love Easter, you’ll love these Easter themed gift tags. They can be used on goodies you share with your family and friends. The download link is below.

For Personal Use Only.




Free – Checkout Checkout




Added to cart




Social Media Star

By
onlinefreecourse
-
0
Social Media Star

Who are your favorite social media stars? What do you like about them?

Would you ever want to be an internet celebrity? Why or why not?

If so, what would your online brand be? What kind of content would you deliver? Rants? Dance videos? Comedy? Gaming tips? Fashion or beauty tips? Something else? Why do you think you’d make a good social media personality?

Tell us in the comments, then read the related article to get advice from the Instagram star, Rickey Thompson, about how to be famous online.

Find many more ways to use our Picture Prompt feature in this lesson plan.

1...1,3141,3151,316...1,385Page 1,315 of 1,385

Recent Posts