SQL Snippets - Crowdsourcing SQL Queries
Written by Nikos Vaggalis   
Friday, 20 August 2021

A curated collection of helpful and reusable SQL snippets sounds like a really good resource to know about and to contribute to.

In SQLBolt - Learn SQL The Interactive Way I highlighted an interactive resource for learning the basics of SQL. Now I've found a way for you to store and share your own snippets of SQL code and find useful code to use without re-inventing it.  

As you work with SQL day by day you'll accumulate a hefty amount of SQL code.  when just maybe you've written a state of the art query that you are eager to share with your colleagues or the rest of the world.That raises two questions:where do you store that snippet for clean and easy retrieval and afterwards how do you share it with the world?

So do you store it in numerous plain .txt files or in your favorite notepad application like Joplin or Notion? Then do you share it by emailing it,uploading it on paste bin and the like or submit it to your GitHub repository?

All these solutions do not scale;for example if you go the GitHub way and someone wants to upgrade your snippet he has to go through forking your repo and then having to submit a pull request for you to amend, while when going Pastebin you can't have things like a discussion, upvoting or favoring the snippet.

The solution to all that is SQL Snippets,a crowdsourced community resource that uses Discourse as the place to share,collaborate and socialize on your snippets.Of course there's also the educational aspect of the matter, where you actually learn by going through those user posted and commented sql queries.

So inside SQL Snippet's premises you can: 

  • Discover helpful snippets
  • Browse by SQL DB type (e.g. BigQuery) or tag (e.g. date-time)
  • Comment, upvote and favorite snippets
  • Submit your own snippets
  • Add bookmarks so you can come back when you need that snippet
  • Request a snippet
  • Upvote posts that you find helpful to help others filter for helpful content
  • Use comments to ask questions and suggest improvements 

You can find the snippets categorized by the following DBMSs: 

  • BigQuery
  • SQL Server
  • Mysql
  • PostgreSql
  • Redshift
  • Snowflake 

and further distil you search by sorting posts by popularity or tags: 

  • analysis
  • clean-data
  • date-time
  • json
  • arrays
  • regex
  • charts
  • generating-data
  • windows-functions 

For example under the PostgreSql category and "date-time" tag we find a snippet named "Filter for last X days", useful in cases you only want to return the last X days of a query (e.g. last 30 days) and you want that to always be up to date (dynamic):

sqlsnippet1

Other snippets you can find are on: 

  • Formatting Dates and Times
  • Split Column to Rows
  • Regex: Validate Email Addresses
  • Parse URL
  • Replace empty strings with NULLs
  • Reverse an array 

As a matter of fact I jumped right in making my very first contribution, Removing duplicate rows, inspired from my oldie SQL Workshop - Removing Duplicate Rows.

As seen in the example above, snippets can also have nested embedded snippets like GitHub gists, but based on the Count platform, a live SQL notebook something which allows running your query on live data and checking the results in real time.

Embeddable snippets are currently in beta and not available to non-privileged users, who for now will have to just use the markdown editor of Discourse to document their snippet. But word is that after this feature comes out of testing it is going to be provided free of charge to all.

So take your code out of your treasure box and put it online.This way you can archive, collaborate, and help others all at the same time.

 

More Information

SQL Snippets

Related Articles

SQLBolt - Learn SQL The Interactive Way

SQL Workshop - Removing Duplicate Rows

SQL Workshop - Selecting columns without including a non-aggregate column in the group by clause

SQL Workshop - Subselects And Join 

 

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Banner


Apple Loosens Its Grip On The App Store But By A Tiny Amount
01/09/2021

This isn't the big result that the Epic Games lawsuit or any of the many anti-trust investigations may result in, but a tiny concession given by Apple in the face of a losing court battle.



Visual Studio 2022 Preview 4 - More Productivity
22/09/2021

Visual Studio 2022 Preview 4 has been released with more support for personal and team productivity, along with support for 'modern development' according to the Visual Studio team.


More News

square

 



 

Comments




or email your comment to: comments@i-programmer.info

Last Updated ( Friday, 20 August 2021 )