Window functions are a powerful tool that helps to leverage the power of PostgreSQL for Data Analysis. In this blog series, I will explain what window functions are, why you should use them, types of window functions and finally will introduce you to some basic window functions in PostgreSQL. In the next few post, I’ll go through more advanced window functions and demo some scenarios. So let’s get going.
Ok, so you’ve stored hierarchical data in a relational database, and written recursive CTEs to query the data and find relationships. Now the application team wants to query hierarchical levels and print the complete ancestry tree. Time to deep dive into some advance CTE constructs.
This is the second post of the series about the Recursive SQL for querying hierarchical data started in the previous post . If you haven’t read it already, I recommend reading it to understand the key concepts:
Self-JoinsCommon Table Expressions (CTE)In this post, we’ll discuss the advanced scenarios like displaying hierarchical levels and printing the “ancestry tree”. Let’s dive in…
Continue reading “Recursive SQL for querying hierarchical data: Part 2 – Levels and Ancestors”
Recently, I was working on an application that required reading hierarchically structured data. And I thought it might be useful to document multiple ways to store and query such hierarchical data (ex. Org. chart, File-system layout, or Set of tasks in a project) in a database. So, let’s jump right in.
Hierarchical data is a specific kind of data, characterized by a hierarchical relationship between the data sets.
Think about data sets having multiple levels: something above, something below, and a few at the same level. A typical example of such hierarchical model is an organizational chart like the one below.
Continue reading “Recursive SQL for querying hierarchical data: Part 1”Average(mean), median, mode are core statistical concepts, that are often applied in software engineering. Whether you are new to programming or have multi years of computer science experience, you’ve likely at some point in time will have used these statistical functions, say to calculate system resource utilization, or network traffic or a website latency. In my current role, my team is responsible for running a telemetry platform to help dev teams measure application performance. We do this by collecting point-in-time data points referred as metrics.
A common use case for metrics is to tell the application latency (i.e. amount of time it took between a user action and web app response to that action). For examples, amount of time it took you to click on twitter photo and till it finally showed up on your device screen. So if you have this metrics collected at regular intervals (say every 1s), you can simply average it over a period of time like an hour or a day to calculate latency. Simple right!
Continue reading “Why Averages suck and what make Percentiles great”
SQL injection is a code injection technique that is capable of destroying your database. It is a code injection technique, used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution. (e.g. to dump the database contents to the attacker)
Continue reading “SQL Injection and Preventing them in your Golang app”
In the last post Updating/Deleting rows with Go, we learned to manipulate rows in PostgreSQL database in Go project using database/sql package that ships with Go, along with github.com/lib/pq Postgres driver. In this post, we’ll learn how to query rows i.e. SELECT
The Go programming language, sometimes referred to as Go lang, is making strong gains in popularity. Chances are if you are a Go developer, you will have to interact with SQL at some point in your project. This blog post will show how to connect to a PostgreSQL database from Go using database/sql package.
VS Code has a rich extension API that let you add languages, debuggers, and tools to your installation to support easy development. PostgreSQL extension allows following:
Continue reading “Site Reliability Engineering: How Google Runs Production Systems – Book Review”First off – It’s worth let you know that Google lets you read this “entire” book online for free on their website. Yes you read it right, you don’t need to buy the book, just click on below link – https://landing.google.com/sre/sre-book/toc/index.html and start reading!
data-nerd.blog 