DEV Community: Abdisalan

5 Edge Cases No Programmer Likes Handling

Abdisalan — Tue, 04 Oct 2022 06:32:39 +0000

Life is usually simple and our clever little programs can handle most things thrown at it... but sometimes, it's not enough.

That's where edge cases come in. Little, one-off, problems we must handle that disrupt the natural flow of our code.

Here's five edge cases I don't like handling as a Software Engineer.

#5 Plural wording(s)

Showing the user "item" when there's one item. Then also showing them "items" when it's more than one or zero.

We've all been guilty of writing “item(s)” with a parenthesis before, right? 🤨 But sometimes, we've gotta fix the bug.

Look at this monstrosity I had to write the other day. I’m not proud of it, but it works.

message = f"You have {len(items)} item{'s' if len(items) > 1 or len(items) == 0 else ''}"

#4 Styling the last element in a list

You've probably run into this if you're coding React or Vue. Imagine you've rendering a list of items like this:

{
    items.map(stuff =>
        <SuperCoolItem key=stuff.key data=stuff style={{marginBottom: 5}} />
    )
}

That bottom margin works well...until the last element. So what do we do?

We check if it’s the last element and then set the margin to zero. If you know a better way to do this, perhaps with a cool CSS trick, leave a comment on how! I would look it up, but I’ve moved on to other tasks ☕

{
    items.map((stuff, index) =>
        <SuperCoolItem key=stuff.key data=stuff style={{
            marginBottom: i == (items.length-1) ? 0 : 5
        }} />
    )
}

#3 Null

Oops! Did you forget to check if the object you were using was actually null? Yea, me too! And in production...

At this point, when I'm not writing TypeScript, I expect these to happen.

JavaScript:
Uncaught TypeError: criticalBusinessObject is undefined

Python:
AttributeError: 'NoneType' object has no attribute 'id'

#2 Time Zones

Oh, your cron jobs were set with a UTC time zone? Really? All our customers are on Pacific time, Abdi! Stop trying to confuse them with a magic timezone no one uses! (sorry to my African and European readers). I’m not even gonna get into it that some time zones are off by 30 minutes 💀.

This is all a lead-in to the real reason I wrote this article. The biggest, most painful issue that comes up twice every year…

#1 Daylight Savings Time

Perhaps the most destructive construct invented by the hubris of man.

Here’s one of the scariest moments in my career. I’m sipping my latte on a beautiful, early spring morning and I hear this:

All our customers got their text messages 1 hour late! I want every engineer in the meeting room now!

— startup CTO

In America, only 48 US states observe it. That leaves Hawaii and most of Arizona (Navajo Nation observes Daylight Saving). Also, only about half the countries in the world observe it, and they don't even agree on when it should happen!

Thanks for reading 😊

Those are my top 5! What are yours? Leave a comment below and let's chat about what we don't like 😼

For my fellow Americans, don’t forget that Daylight Savings is November 6 this year and the clocks are falling back. “Spring forward, Fall back” — good way of remembering.

Photo by Elisa Ventur on Unsplash

How One Developer Recreated AirDrop Using Just JavaScript

Abdisalan — Sat, 12 Dec 2020 21:15:50 +0000

Have you ever used AirDrop on iPhone or Mac?

Okay, If you haven't then can you imagine seamlessly sharing files between phones or a laptop at the tap of a button?

SnapDrop.net, created by Robin Linus, lets you directly share files between any device with a browser. Doesn't matter if it's between iPhone and Android or a tablet with a PC.

And no uploading to and downloading from the cloud needed. 😲

So how the heck does it work?

After analyzing it line by line, I figured out its brilliant architecture. In this post I'll show you how it works.

Use This New Technology

Knowing this technology could set you apart from other engineers who haven't yet explored what it has to offer.

This awesome technology is WebRTC (Web Real-Time Communication) and it only came out a few years ago. Its data channel enables SnapDrop to send bytes (even audio and video!) directly from one peer to another.

(Think of a peer as a device, like your phone or laptop)

However, WebRTC can't connect two users without some help. It needs a signaling server, in other words, something to discover other peers and show it how to connect.

WebRTC is Impossible To Use Without This

ICE (Interactive Connectivity Establishment) is how a computer can draw a map from the internet to itself when it doesn't have a public IP address. This is due to NAT (Network Address Translation) that happens between your router and computer.

Once the map has been made, you'll have find some way for these two devices to share their maps with each other. SnapDrop does this through a NodeJS server that communicates between each peer using WebSockets — another awesome protocol.

Now you're probably thinking, is this secure?

Okay, But How Do You Secure This Thing?

WebRTC in transit encrypts its data by default. That's cool and all but you also probably don't want to be sharing files with random people.

SnapDrop only shares the ICE between two computers with the same IP address — meaning they're on the same network/wifi.

It does this by creating rooms for each IP address and differentiates devices by generating a unique id.



/* Code to handle joining peers from the server */
_joinRoom(peer) {
    // if room doesn't exist, create it
    if (!this._rooms[peer.ip]) {
      this._rooms[peer.ip] = {};
    }

    // add this peer to room
    this._rooms[peer.ip][peer.id] = peer;
}

You might not want to use this app while on public wifi since then anyone could send files to you. But in this pandemic, who's going outside anyway? 🤷‍♀️

The code snippet above makes an interesting choice by storing the peers in an object on the server class. Normally you would expect a database to be used but this might be to simplify things and the app probably doesn't have a lot of traffic.

A Familiar User Interface and User Experience

The style is almost exactly like AirDrop. Each device has a fun name and an icon to help distinguish each peer. Not only that but its also a progressive web app which gives it a few nice features like:

Feels like a native app
Notifications
Live Updates

What If The Device Doesn't Support WebRTC?

By now most devices/browsers support WebRTC but in the event that they don't, SnapDrop has a fallback! In this case it uses the already established WebSocket connection to send the file data.

However, this is less efficient and less secure because the data needs to first go to the server and before it reaches its final destination.



if (window.isRtcSupported && peer.rtcSupported) {
    this.peers[peer.id] = new RTCPeer(this._server, peer.id);
} else {
    this.peers[peer.id] = new WSPeer(this._server, peer.id);
}

Event-Driven Code Style

The code base is completely event-driven. You use this style when you want to decouple services from each other and allow processing as actions occur.

This compliments WebRTC and WebSockets because they're also event-driven. When a message comes in, or a new peer joins, or a file wants to be sent — that's an event.

It's really tough to follow at first because its not a linear process. Here's the class for registering and firing events.



class Events {
    static fire(type, detail) {
        window.dispatchEvent(new CustomEvent(type, { detail: detail }));
    }
    static on(type, callback) {
        return window.addEventListener(type, callback, false);
    }
}

Which lets you write event-driven code like this



Events.on('signal', e => this._onMessage(e.detail));

Events.on('peers', e => this._onPeers(e.detail));

Events.on('files-selected', e => this._onFilesSelected(e.detail));

Events.on('send-text', e => this._onSendText(e.detail));

Events.on('peer-left', e => this._onPeerLeft(e.detail));

Check Out The Code Yourself

I hope you learned something today! If you want to explore the code for yourself, here's the github repository. https://github.com/RobinLinus/snapdrop

The creator was also kind enough to create a docker compose file so that you could run and host this yourself. I wonder how many people are running their own SnapDrop instance?

Thanks for Reading!

What do you think of this type of blog post? I feel like I had to write about it because this project taught me a few valuable lessons. Leave comment below and I'll get back to everyone!

See you in the next one ✌

P.S. Support SnapDrop

Wow! Didn’t think this would be so popular! 10,000+ of you have seen this article!

Please consider supporting the project because it is free and has NO ADs and NO data collection.

Link here: https://github.com/RobinLinus/snapdrop#support-the-snapdrop-community

How to Code a Video Streaming Server using MongoDB

Abdisalan — Sat, 28 Nov 2020 02:21:00 +0000

Do you want to create a video streaming app and store videos in MongoDB? If you JUST want to learn about the streaming part, I made another (kind of successful) post on it using purely NodeJS.

BUT!

If you also want to know how to store your videos in MongoDB and stream them from there through NodeJS, this is the post for you!

Final Result

Just like the pure NodeJS solution, our final result will be a single HTML5 video that's streaming video from our server. Look at the grey bar in the timeline! That's buffering! 😃

If you want to git clone the project, here's the link to the repo https://github.com/Abdisalan/blog-code-examples/tree/master/mongo-http-video

Easy Mode

If you have docker installed, you can use my docker compose file to run the project without needing to install any packages. You just need to run these docker compose commands as well as copy the bigbuck.mp4 file from the http-video-stream folder to the mongo-http-video folder.



docker-compose up -d
# When you're done with it, you can tear it down using
docker-compose down -v

And the project will be running on localhost:8000

The rest of the article is instructions on how to build it from scratch. Enjoy!

Part 1: MongoDB

MongoDB does not support Windows Subsystem for Linux (WSL), so you're better off using docker if you want to use WSL. Otherwise this should work in Windows just fine.

Install MongoDB, and if you're on windows these next commands (exe files) should be located in C:\Program Files\MongoDB\Server\4.4\bin

Open a terminal in that folder OR add it to your PATH and start the mongod process.



mongod

Part 2: Setup Node Project

In another terminal, these commands will create a project folder and start your node project.



mkdir mongo-video-stream
cd mongo-video-stream
npm init
npm install --save express nodemon mongodb

Part 3: index.html

We need to create an page with an HTML5 Video element and set the source to "/mongo-video" where our server will stream the video from mongoDB.



<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>HTTP Video Stream From MongoDB</title>
  </head>
  <body>
    <video id="videoPlayer" width="650" controls muted="muted" autoplay>
      <source src="/mongo-video" type="video/mp4" />
    </video>
  </body>
</html>

Part 4: Index.js

Lets setup our node server so that the root "/" endpoint serves our index.html page.



const express = require("express");
const app = express();

app.get("/", function (req, res) {
  res.sendFile(__dirname + "/index.html");
});

app.listen(8000, function () {
  console.log("Listening on port 8000!");
});

Part 5: package.json -- Run our server

Add a start script to your package.json so that we can run our server using npm start command.



{
  "scripts": {
    "start": "nodemon index.js"
  }
}

Now you should be able to run npm start. Open your browser and go to http://localhost:8000 to see if it worked!

Midway Check In

How are you doing? Maybe drink some water, fix your posture, and relax your shoulders 😁

You're about to get to the good part!

Part 6: index.js (Uploading)

We add an endpoint where we can upload a local video to mongodb. The video file I'm using is bigbuck.mp4 and can be found on my github at https://github.com/Abdisalan/blog-code-examples/tree/master/http-video-stream

However you can use your own video file!



const express = require("express");
const app = express();
const fs = require("fs");
const mongodb = require('mongodb');
const url = 'mongodb://localhost:27017';

app.get("/", function (req, res) {
  res.sendFile(__dirname + "/index.html");
});

// Sorry about this monstrosity -- just for demo purposes
app.get('/init-video', function (req, res) {
  mongodb.MongoClient.connect(url, function (error, client) {
    if (error) {
      res.json(error);
      return;
    }
    // connect to the videos database
    const db = client.db('videos');

    // Create GridFS bucket to upload a large file
    const bucket = new mongodb.GridFSBucket(db);

    // create upload stream using GridFS bucket
    const videoUploadStream = bucket.openUploadStream('bigbuck');

    // You can put your file instead of bigbuck.mp4
    const videoReadStream = fs.createReadStream('./bigbuck.mp4');

    // Finally Upload!
    videoReadStream.pipe(videoUploadStream);

    // All done!
    res.status(200).send("Done...");
  });
});

app.listen(8000, function () {
  console.log("Listening on port 8000!");
});

After saving the index.js file, your server should restart (because we're using nodemon). Once you have the video ready, you can go to localhost:8000/init-video in your browser and it should have uploaded your local file to mongodb!

Almost there!

If you want to double check that the file was uploaded, open another terminal and connect to mongodb.



mongo

Then go to the videos database and count the number of documents in the fs.files collection by GridFS.



use videos
db.fs.files.count()

The count should be how many times you went to localhost:8000/init-video because it uploads our video file every time you load /init-video.

Part 7: index.js (Streaming)

Lastly, we're gonna add the /mongo-video endpoint to stream our video!



app.get("/mongo-video", function (req, res) {
  mongodb.MongoClient.connect(url, function (error, client) {
    if (error) {
      res.status(500).json(error);
      return;
    }

    // Check for range headers to find our start time
    const range = req.headers.range;
    if (!range) {
      res.status(400).send("Requires Range header");
    }

    const db = client.db('videos');
    // GridFS Collection
    db.collection('fs.files').findOne({}, (err, video) => {
      if (!video) {
        res.status(404).send("No video uploaded!");
        return;
      }

      // Create response headers
      const videoSize = video.length;
      const start = Number(range.replace(/\D/g, ""));
      const end = videoSize - 1;

      const contentLength = end - start + 1;
      const headers = {
        "Content-Range": `bytes ${start}-${end}/${videoSize}`,
        "Accept-Ranges": "bytes",
        "Content-Length": contentLength,
        "Content-Type": "video/mp4",
      };

      // HTTP Status 206 for Partial Content
      res.writeHead(206, headers);

      // Get the bucket and download stream from GridFS
      const bucket = new mongodb.GridFSBucket(db);
      const downloadStream = bucket.openDownloadStreamByName('bigbuck', {
        start
      });

      // Finally pipe video to response
      downloadStream.pipe(res);
    });
  });
});

Save the file and go to localhost:8000 one more time and the video should be streaming!

Conclusion

With this, you can make your own basic YouTube or Netflix app!

For an in-depth line by line explanation with theory on how this works, consider watching my YouTube video.

This was a basic overview of how to get this working, if you want any in-depth blog posts on any of these topics (mongodb, streaming theory), feel free to comment down below!

Happy Streaming! ✌

Disclaimer

You probably shouldn't use this in production because its not the most optimized architecture 😋

How to Code a Video Streaming Server using NodeJS

Abdisalan — Sun, 25 Oct 2020 01:52:05 +0000

Do you want to stream video in your app without needing users to download the entire video? Here's how to do exactly that using NodeJS.

Final Result

Here's the end result of what we're gonna make.

Notice that light grey bar on the video timeline? That's the HTML5 Video Element buffering the video from our NodeJS server!

If you want to git clone the code and play with it yourself, here's the link to my GitHub Repo! https://github.com/Abdisalan/blog-code-examples/tree/master/http-video-stream

Part 1: Setup npm project

You'll need to install NodeJS and run:

mkdir http-video-stream
cd http-video-stream
npm init
npm install --save express nodemon

Part 2: index.html

We need to create a HTML5 Video element, and set the source as "/video", which is where server's endpoint is.

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>HTTP Video Stream</title>
  </head>
  <body>
    <video id="videoPlayer" width="650" controls muted="muted" autoplay>
      <source src="/video" type="video/mp4" />
    </video>
  </body>
</html>

Part 3: index.js

Now lets setup our node server so that on "/" endpoint it serves our index.html page.

const express = require("express");
const app = express();

app.get("/", function (req, res) {
  res.sendFile(__dirname + "/index.html");
});

app.listen(8000, function () {
  console.log("Listening on port 8000!");
});

Part 4: package.json -- Run our server

Add a start script to your package.json so that we can run our server using npm start command.
There's more in your package.json file but I just want you to copy this start script. It uses nodemon to run index.js and restarts the server every time you save the index.js file so you don't need to restart the server yourself!

{
  "scripts": {
    "start": "nodemon index.js"
  }
}

Now you should be able to run

npm start

and see our app running on port 8000. Open your browser and go to http://localhost:8000 to see if it worked.

Part 5: index.js (Again)

We're almost there!
For this final stage, you'll need to either find an mp4 video file, or download the one I've provided in my GitHub project link.
https://github.com/Abdisalan/blog-code-examples/tree/master/http-video-stream
Here's the "/video" endpoint for our server.

// in the imports above
const fs = require("fs");

app.get("/video", function (req, res) {
  // Ensure there is a range given for the video
  const range = req.headers.range;
  if (!range) {
    res.status(400).send("Requires Range header");
  }

  // get video stats (about 61MB)
  const videoPath = "bigbuck.mp4";
  const videoSize = fs.statSync("bigbuck.mp4").size;

  // Parse Range
  // Example: "bytes=32324-"
  const CHUNK_SIZE = 10 ** 6; // 1MB
  const start = Number(range.replace(/\D/g, ""));
  const end = Math.min(start + CHUNK_SIZE, videoSize - 1);

  // Create headers
  const contentLength = end - start + 1;
  const headers = {
    "Content-Range": `bytes ${start}-${end}/${videoSize}`,
    "Accept-Ranges": "bytes",
    "Content-Length": contentLength,
    "Content-Type": "video/mp4",
  };

  // HTTP Status 206 for Partial Content
  res.writeHead(206, headers);

  // create video read stream for this particular chunk
  const videoStream = fs.createReadStream(videoPath, { start, end });

  // Stream the video chunk to the client
  videoStream.pipe(res);
});

The HTML5 video element makes a request to the /video endpoint, and the server returns a file stream of the video, along with headers to tell which part of the video we're sending over.

For a chunk size, I've decided 1MB but you could change that to whatever you like! Another great benefit of this is that we don't need to code the stream to continuously deliver the video data, the browser handles that gracefully for us.

For an in-depth line by line playback on how this works, consider watching my YouTube video on this topic.

Now, you've got a working video streaming server using NodeJS!

Happy Streaming! ✌

4 Ways To Traverse Binary Trees (with animations!)

Abdisalan — Mon, 07 Sep 2020 02:41:37 +0000

Brush up with Tree Data Structure

Here's a Binary Search Tree (BST). Every circle is called a node and each node can be connected to 2 other nodes -- one on the left and right. That's why they're called Binary Trees, they have 2 "child" nodes and it looks like a tree!

The left child node is always smaller or equal in value than the parent, and the right is always greater or equal. Some implementations allow only the left or right node to be equal to the parent. Personally, I like putting equal nodes, to the left side 😁

BTW, here's a Binary Search Tree using python3!



class TreeNode:
  def __init__(self, val=0, left=None, right=None):
    self.val = val
    self.left = left
    self.right = right

The default values for the left and right tree are null/None and default val is 0.

Traversals

For each traversal I'm going to give a brief description of how it moves through the binary tree, starting from the root (the top). Then show the code for traversal using python and lastly a GIF visualizing how the program moves through the tree! Also all of the gifs will end early without going through the entire tree because the gif sizes were getting too big and I had to cut it short 🤷‍♂️.

The first 3 are very similar and only differ by one line of code but give very different results! I call them the traversal triplets.

In Order

This method moves through the tree "in order". Meaning that it will print every node’s value in order from smallest to greatest.

Using recursion, the function will call itself on the left sub-tree, then print the current value, and then call itself on the right sub-tree.
The result is that it prints the tree's values.... in order.



def in_order(node):
    if node == None:
        return
    in_order(node.left)
    print(node.val)
    in_order(node.right)

Depth-First Search (also known as pre-order)

Depth-First Search is the classic traversal method for trees and also graphs. This method is also known as the pre-order traversal because it first operates on the current node, then the left and right child nodes.

Depth first search is very popular for general traversal as well as making copies of the tree.



def dfs(node):
    if node == None:
        return
    print(node.val)
    dfs(node.left)
    dfs(node.right)

Post Order

The last of the traversal triplets. It first operates on the left and right child nodes then the current node.

Post Order is very useful for deleting a tree, as it goes from the bottom up.



def post_order(node):
    if node == None:
        return
    post_order(node.left)
    post_order(node.right)
    print(node.val)

Breadth-First Search (also Level Order)

This one is a little more tricky and can only be done using a queue. It might be possible with recursion but it's easier to understand iteratively. It uses a queue when traversing so it goes through the tree as nodes are added to it. As a result, it goes through the tree, level by level. This makes breadth-first search a popular search algorithm in graphs as well.

Although I couldn't make an animation for this one, I created a numbered diagram to show what order the nodes would be printed!



# deque is a python queue library
from collections import deque

def bfs(root):
    q = deque([root])
    while len(q) > 0:
        node = q.popleft()
        print(node.val)
        if node.left:
            q.append(node.left)
        if node.right:
            q.append(node.right)

Conclusion

By understanding these 4 traversal methods, you can demystify most interview problems involving binary trees. A lot of them can be broken down into easily solvable chunks if you know the right traversal method to use! If you're curious about the exact methodology, I've got a guide coming out soon! I hope it'll be the only resource needed for solving binary tree problems.

Also, all animations were courtesy of http://btv.melezinek.cz/binary-search-tree.html. Make sure to check them out if you want to play around with the animations!

By The Way I'm Making a Guide

I'm making a guide on how to solve almost any Binary Tree problem on LeetCode (and therefore in an interview)! Join me on my journey by following me on Twitter!

Here's a sneak peek 😃
// Detect dark theme var iframe = document.getElementById('tweet-1300153885642776576-197'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1300153885642776576&theme=dark" }

Cracking the Binary Tree Interview

Abdisalan — Sat, 29 Aug 2020 18:31:37 +0000

Hi everyone!

I recently finished 3 months of interviewing, and I received multiple offers! It was no walk in the park because my data-structures knowledge wasn't the best.

In particular, I was terrible at solving binary tree questions. So I practiced and practiced and practiced until I became confident I could solve almost any binary tree questions on LeetCode/HackerRank.

I want to prepare a guide that explains my process for solving these questions to help anyone struggling in this area or just want to refine their skills.

My question is, what would you expect from a complete guide on solving Binary Tree questions? My working outline so far is as follows:

Foundation
-Tree DS: class, insertion, deletion, array-based trees
-All Tree Traversals (recursive)
-All Tree Traversals (iterative)
-Tree specific Algorithms

How to Solve
-How to determine the type of question and solution (my guide)
-Common gotchas
-20+ Question Solution Explanations

Do you feel like something is missing? Please comment on any ideas you have or if you want to receive this guide when it releases!

I'll be working on this over the coming weeks and releasing it in pieces over twitter and devto. If you want to follow my process and get a behind the scenes look, follow me on Twitter!

Photo by Zane Lee on Unsplash

How to Measure Cohort Retention using BigQuery

Abdisalan — Fri, 28 Aug 2020 22:02:27 +0000

With some simple visitor data, you can tell how you're retaining your customers on a month by month basis. That's super valuable!

You can follow along at home. On the Google Cloud Platform, we'll use one of BigQuery's public datasets (details below). It's real visitor data from the Google Store in 2017.

If you want to use your own data, the queries in this guide can be converted to work with your favorite SQL dialect. All your data needs is a timestamp of when your users visit your site and a visitor ID to track them.

What This Analysis Will Produce
Cohort Retention Definition
Cohort Retention Framework
BigQuery Data
SQL
Part One: Find Our Cohorts
Part Two: Measure Activity After Cohort Month
Final: Divide the Number of Remaining Users by the Cohort Size
Conclusion
Future Use and Modifications

What This Analysis Will Produce

Here's a graph showing what percent of users came back to the Google Store after visiting for the first time. It's grouped by what month they first visited and shows what percent remain after subsequent months.

And here are the cohort sizes, for the more detail oriented folks.

Cohort Retention Definition

What's a cohort? Essentially a group of people.

How are we grouping our customers? I've decided to group them by the month that they visited the site.

How do we know if we retained a user? We can know if they visit the site again in a subsequent month. For example if they visited in June then again in July, they were retained. But the months are independent — They could visit in June, and not show up again until August. We wouldn't count July, but would count August.

Cohort Retention Framework

Group all our users into cohorts based on the month they first came to the store and count the total.
Count how many users came back each month, starting from their cohort month.
Calculate the percentage of cohort remaining after each month

BigQuery Data

Feel free to skip this section if you don't want to use the example data from BigQuery.

BigQuery has a large number of public datasets and Google Store Analytics from 2017 is one of them.

You'll need to make a Google Cloud Platform account, create a project, and add the public dataset to your project. Next, since we can't edit the public dataset, you'll need to copy the data from the public dataset to your project.

SQL

In BigQuery, I named my project "Cohort Analysis" and named my duplicate table "Google Store Analytics" so you'll see the prefix cohort-analysis-284422.Google_Store_Analytics often in this article. To follow along, replace this prefix with the name of your project and table in BigQuery.

For each part, I'll also be creating views to make the queries reusable and easier to work with!

Part One: Find Our Cohorts

First we find out the first time each customer visited the site this year and create a view so that we can reference this query later.

create view `cohort-analysis-284422.Google_Store_Analytics.cohort` as
select
    extract(date from timestamp_seconds(min(visitStartTime))) as cohort_month,
    fullVisitorId
from `cohort-analysis-284422.Google_Store_Analytics.ga_sessions_2017*`
group by fullVisitorId

Example Results:
cohort_month    fullVisitorId   
2017-05-12    0206596068278667912
2017-05-12    3310123534725675314
2017-04-25    3153380067864919818
2017-06-23    1216639212736323098

We also find out the size of each cohort by counting the number of unique ids that show up for the first time in a month.

create view `cohort-analysis-284422.Google_Store_Analytics.cohort_size` as
select
    extract(month from cohort_month) as cohort_month,
    count(1) as num_users
from `cohort-analysis-284422.Google_Store_Analytics.cohort`
group by cohort_month

Example Results:
cohort_month    num_users   
1             52613
2             49133
3             55346
4             52873

Part Two: Measure Activity After Cohort Month

Next, we find what months there's been activity after their cohort month. For example If their cohort was January and they came to the site on February, then it notes activity on month one — one month after their cohort month.

create view `cohort-analysis-284422.Google_Store_Analytics.user_activities` as
select date_diff(
    extract(date from timestamp_seconds(GA.visitStartTime)),
    C.cohort_month,
    MONTH
) as month_number,
GA.fullVisitorId
from `cohort-analysis-284422.Google_Store_Analytics.ga_sessions_2017*` GA
left join `cohort-analysis-284422.Google_Store_Analytics.cohort` C
on GA.fullVisitorId = C.fullVisitorId
group by 2, 1

Example Results:
month_number    fullVisitorId
0             7868243379843705173
0             5418231530547311489
0             4704421551407457903

Now we count how many users were retained in each month after their cohort month and name it retention_table.

create view `cohort-analysis-284422.Google_Store_Analytics.retention_table` as
select
    extract(MONTH from C.cohort_month) as cohort_month,
    A.month_number,
    count(1) as num_users
from `cohort-analysis-284422.Google_Store_Analytics.user_activities` A
left join `cohort-analysis-284422.Google_Store_Analytics.cohort` C ON A.fullVisitorId = C.fullVisitorId
group by 1, 2

Example Results:
cohort_month    month_number    num_users
1             0             52613
1             1             2198
1             2             908

Final: Divide the Number of Remaining Users by the Cohort Size

Finally we get the cohort percent retention after each month.

create view `cohort-analysis-284422.Google_Store_Analytics.final` as
select
  R.cohort_month,
  S.num_users as total_users,
  R.month_number,
  CAST(R.num_users as float64) * 100 / S.num_users as percentage
from `cohort-analysis-284422.Google_Store_Analytics.retention_table` R
left join `cohort-analysis-284422.Google_Store_Analytics.cohort_size` S ON R.cohort_month = S.cohort_month
where R.cohort_month IS NOT NULL
order by 1, 3

You should end up with a final result that looks like this.

With this data I also made this bar graph to show the drop off in users each month.

Conclusion

Again, figuring out how you're retaining your users is super valuable! You can see in this chart that the January cohort is consistently more retained than other cohorts. This could be for various reasons and knowing why could help your business capitalize on this fact.

Future Use and Modifications

Anyone who has used Google Analytics on their site knows that this cohort analysis is immediately available to them for free. But this manual method is much more flexible!

Imagine that you also want to find retention numbers for customers who have paid for a product? What about customers who have used your latest feature or were referred by a friend? There are tons of insights you can get from visitor data which makes this data super powerful for guiding your business.

Thank you for reading and I'll see you in the next article!

✌🏾

SQL Practice Problem to Help You Pass Your Interview

Abdisalan — Fri, 21 Aug 2020 22:23:42 +0000

In the 50+ programming interviews I've done, I've only been asked two SQL questions.

I failed both of those questions.

While I won't give the questions away, I will give you a problem to practice so that you can succeed where I failed!

This question combines many principles you'll need to quickly solve SQL interview problems.

Hopefully you'll be better prepared than I was 😅

The Dog Database

Imagine you're running a dog shelter and you have a database of dogs and owners. Every dog has one owner, but owners can have many dogs.

Here's the owner and dogs table written in PostgreSQL.

Owner Table

CREATE TABLE owners (
  id SERIAL PRIMARY KEY,
  name VARCHAR(256)
);

Dogs Table

CREATE TABLE dogs (
  id SERIAL PRIMARY KEY,
  owner_id INTEGER REFERENCES owners(id),
  breed VARCHAR(256),
  adopted_on TIMESTAMP
);

Note that there is a one to many relationship between the owners and dogs table. There is one owner id tied to each dog and enforced by a foreign key contraint i.e. you can only use owner ids that actually exist in the owners table.

The Question

Now, that the tables are setup, we can get to the real question.

Write an SQL query that gives the latest dog each owner adopted along with the name of the owner.

Pretty simple right?

Here's some example data to help you out.

owners
 id |  name
----+--------
  1 | PersonA
  2 | PersonB

dogs
 id | owner_id |   breed   | adopted_on
----+----------+-----------+--------------
  1 |        1 | chow chow | 2019-02-03
  2 |        2 | dalmation | 2019-03-07
  3 |        2 | beagle    | 2020-09-21
  4 |        1 | pit bull  | 2020-08-01

The answer to the question should give you a result that looks like this.

✅ RESULT
name      |   breed  | adopted_on
----------+----------+------------
PersonB   | beagle   | 2020-09-21
PersonA   | pit bull | 2020-08-01

Try this out for yourself first, then I'll go over the answer below. Don't worry about setting this up on your computer! Here's an SQL Fiddle (like CodePen but for SQL) for you test your answer!

http://sqlfiddle.com/#!17/5059f/10

Final Answer

Let's go through this step by step. There's probably a few other ways of doing this but this is mine.

Part 1: Getting the newest dogs

First we find each newest adoption date by each owner.
To do this, I use the max function on the adopted_on column after grouping by owners. I make sure to also get the owner_id, that way we can use it to join on another table.

SELECT owner_id, max(adopted_on) FROM dogs GROUP BY owner_id

RESULT
owner_id |     max
---------+--------------
       1 |  2020-09-21
       2 |  2020-08-01

Part 2: Getting the breed of the newest dogs

Next, we join the last query with the dogs table (itself) to get the breed of the dog and match by the adoption date as well as the owner.

SELECT dogs.breed, dogs.adopted_on FROM dogs
JOIN (
  SELECT owner_id, max(adopted_on) FROM dogs GROUP BY owner_id
) AS newest_dogs
ON
    dogs.owner_id = newest_dogs.owner_id AND
    dogs.adopted_on = newest_dogs.max;

RESULT
  breed    |  adopted_on 
-----------+--------------
  beagle   |  2020-09-21
  pit bull |  2020-08-01

Final: Get the names of the owners

Lastly, we join the result of the last query on the owners table to get their name.

SELECT owners.name, dogs.breed, dogs.adopted_on FROM dogs
JOIN (
  SELECT owner_id, max(adopted_on) FROM dogs GROUP BY owner_id
) AS newest_dogs
ON
  dogs.owner_id = newest_dogs.owner_id AND
  dogs.adopted_on = newest_dogs.max
JOIN
owners ON dogs.owner_id = owners.id;

✅ FINAL RESULT
name      | breed    | adopted_on
----------+----------+------------
PersonB   | beagle   | 2020-09-21
PersonA   | pit bull | 2020-08-01

Conclusion

Although the question was simple, there were a few tricky queries we had to make! We needed to join tables two times and find the max aggregate on one of the tables.

I hope you learned something from this exercise! If you want to experiment with my final answer, I've also included a SQL Fiddle with the final answer below.

http://sqlfiddle.com/#!17/5059f/9

Thanks for reading! If you want more content, follow me on twitter!

Cover Photo by Berkay Gumustekin on Unsplash

✌️

1 SQL Query You Should Stop Using

Abdisalan — Tue, 16 Jun 2020 16:04:21 +0000

Let's talk about pages. You know, like this

or infinite scrolling pages like this

Because we never want to give our website visitors all of our data, we present them in pages and let users load more as they please.

One method of paging, AKA pagination, in SQL that's easy is to limit a query to a certain number and when you want the next page, set an offset.

For example, here's a query for the second page of a blog

SELECT * from posts
ORDER BY created_at
LIMIT 10
OFFSET 10

However, for larger databases, this is not a good solution.

To demonstrate, I created a database and filled it 2,000,000 tweets. Well, not real tweets but just rows of data.

The database is on my laptop and is only 500mb in size so don't worry too much about the specific numbers in my results but what they represent.

First, I'm going to explain why using offset for pagination is not a good idea, and then present a few better ways of doing pagination.

Offset Pagination

Here's a graph showing how much time it takes to get each page. Notice that as the page number grows, the time needed to get that page increases linearly as well.

Results:
200,000 rows returned
~17.7s
~11,300 rows per second
** unable to paginate all 2 million rows under 5 minutes

This is because the way offsets works are by counting how many rows it should skip then giving your result. In other words, to get the results from rows 1,000 to 1,100 it needs to scan through the first 1,000 and then throw them away. Doesn't that seem a bit wasteful?

And that's not the only reason why offset is bad. What if in the middle of paging, another row is added or removed? Because the offset counts the rows manually for each page, it could under count because of the deleted row or over count because of a new row. Querying through offset will result in duplicate or missing results if your data is ever-changing.

There are better ways to paginate though! Here's one

Order Based Pagination

You can page on pretty much anything you can order your data by.

For example, If you have an increasing id you can use it as a cursor to keep track of what page you were on. First, you get your results, then use the id of the last result to find the next page.

SELECT * FROM tweet
WHERE id <= $last_id
ORDER BY id DESC
LIMIT 100

2,000,000 rows returned
~4.2s
~476,000 rows per second

This method is not only much faster but is also resilient to changing data! It doesn't matter if you deleted a row or added a new one since the pagination starts right where it left off.

Here's another graph showing the time it takes to page through 2 million rows of data, 100 rows at a time. Notice that it stays consistent!

The trade-off is that it cannot go to an arbitrary page, because we need the id to find the page. This is is a great trade-off for infinite scrolling websites like Reddit and Twitter.

Time Pagination

Here's a more practical example of pagination based on the created_at field.

It has the same advantages and one drawback as ID pagination. However, you will need to add an index for (created_at, id) for optimal performance. I included id as well to break the tie on tweets created at the same time.

CREATE INDEX on tweet (created_at, id)

SELECT * from tweet
WHERE (created_at, id) <= ($prev_create_at, $prev_id)
ORDER BY created_at DESC, id DESC
LIMIT 100

2,000,000 rows returned
~4.70s
~425,000 rows per second

Conclusion

Should you really stop using OFFSET in your SQL queries? Probably.

Practically, however, you could be completely fine with slightly slower results since users won't be blazing through your pages. It all depends on your system and what trade-offs you're willing to make to get the job done.

I think offset has its place but not as a tool for pagination. It is much slower and less memory efficient than its easy alternatives. Thanks for reading!

✌🏾

How I get into “The Zone”

Abdisalan — Sun, 08 Mar 2020 16:15:26 +0000

Photo by Matteo Di Iorio on Unsplash

For those who don’t know, "the zone" refers to a mental state where one’s focus is solely on their craft and they can produce some of their best work.

The way I get into the zone is pretty simple, so I thought I’d share.

The right setting ⏰

After I’ve arrived at work and had my daily standup meeting with the team, I usually don’t have any meetings scheduled for a couple hours. Perfect time to get in the zone!

Caffeine ☕️ (usually but optional)

I get a cup of coffee and start sipping as I go through my email, after about 15 minutes I'm finally awake for the first time.

Meditation 🧘🏾‍♂️ (critical!)

This is necessary for me because at the start of the day, I'm a little stressed about what needs to be done and in general have too much going on in my head.

It helps me to write one or two tasks I intend to accomplish that morning and then meditate to clear my mind of everything.

I put on headphones and listen to a guided meditation on headspace. I need a minimum of 10-15 minutes before my body is relaxed, and my mind has calmed down.

Once all that's done, I'm instantly in the zone. My to-do list is in front of me and I burst into action!

What's your morning routine like? How do you get in the zone?

3 Tips If You're Afraid To Start A Project from Scratch

Abdisalan — Fri, 28 Feb 2020 05:09:47 +0000

✨ You Can Do This! I Believe In You! ✨

Some words of encouragement before you start your big formidable coding project.

Starting is the absolutely the hardest part, but it can take as little as making the git repo, or writing the bare minimum to get something running even if its <html></html>.

Share what you want to work on so that your friends can hold you accountable. It works even better if you both start a project and encourage each other to keep going!

No Bug Is Unsolvable

Personally, this has discouraged me a lot.

One thing that really frustrated me when I was starting out was that I was afraid I would run into a bug in my code or it wouldn't compile and no one could help me fix it.

And sometimes it did happen. Entire projects I've poured hours and hours into that just fall to the way side because of that one bug that ruined the whole project.

My biggest mistake was not reaching out. There are so many resources for people to check over your code, you're just going to have to be willing to share.

Ask the internet, and you shall receive. Sometimes more than you want, but you'll likely get an answer if you post your issue online.

Ask on StackOverflow, even though it can be intimidating but you'll have to push through it!

Heck, ask me! Go to my twitter @abdisalan_js and send me a DM of your bug and maybe I'll be able to help!

Pick One Area and Get Good; Ignore the Other Fluff

When I got started, I tried to master the entire profession. I did courses on C++ game development, Javascript, Python, 3d engine building, web development, php and more.

I don't remember 99% of it.

There's no strict areas or category, but sticking to one programming language vs many is a good start. If you've got some experience and are comfortable with one language, you could expand to a different language that works with it. For example, if you learned javascript for web development, it could help to learn python for making web APIs.

The moral is to keep the vision narrow for now so that you can get better faster and build on your confidence.

Its also good for job search, which you'll eventually have to do. When employers look at your CV, they'll want to put you in a category, and you want to be strong in one category at least. That's a discussion for another time though.

Bonus: Try to collaborate!

Don't try to do this all on your own! Find someone, anyone, who might want to work on the project with you. Having someone else there can be a big motivator and you can get a lot more done.

If you're both familiar with git, then this could be a good time to try sharing a repo and reviewing each others' code.

Comment down below what project you're working on! Maybe someone else will be interested and make a pull request?

Thanks for reading and I hope this helped you start that project you've been thinking of!

1 Weird CSS Property You Should Use In Your Next Project

Abdisalan — Fri, 21 Feb 2020 05:38:19 +0000

This article was originally published on my blog

First, we need to familiarize you with the CSS Box Model.

Skip to the quiz in the next section if you already know the box model

CSS Box Model

The box model describes the basic structure of one html element. Note that this is not to scale, so the pixels do not match up in the image!

The blue dotted box is the width x height of the content in the html element
Purple space is padding in the html element that surrounds the content with empty space
Border is the line around the html element. Used to set a border around the element.
Margin is like padding (empty space) but goes outside the border — determines space between elements.

Understand? Good you're ready for the quiz

Quick CSS Quiz

Imagine that a you have a div with a width of 50px, height of 5px, a 1px border, and 10px of padding all around it.

What is the total width of this div, including the border and padding?

⏳

Answer: 72px

To get that answer you had to start with the width of 50px add the left-side and right-side of the border for 2px and lastly add the left-side and right-side padding for 20px.

But it doesn't have to be this way!

Why did we need to make all these calculations to measure a simple width of a div? Why can't the css width actually refer to the width of the div?

Well there is another box-model we can use!

Introducing: `box-sizing` css property

First, I'll go over the default value for box-sizing, which is content-box. Then I'll go over the alternative setting, border-box!

In short, with content-box the width and height properties in css refer to the width and height of the html content, not including padding and border.
With border-box the width and height properties refer to the total width and height of the html, which includes the padding and border.

content-box (default)

If box-sizing: content-box property is set, the width and height property only applies to the content section. The resulting width of the entire html element is 72px, including the extra 20px from padding and 2px from the border. And the resulting height is 5px.

✨border-box ✨

If box-sizing: border-box property is set, the width and height property is applied to the entire element. The content section is calculated after including the padding and border so the width of the entire child element is 50px with a content width 28px, padding of 20px and 2px of border. The height of the content is now calculated to be 0 because the 10px padding on top and bottom satisfy the 5px height requirement.

Playable CodePen

I made demo in CodePen! Feel free to open up the inspector and see the changing width.

When the box-sizing property is set to content-box, the width is 72px.

When the box-sizing property is set to border-box, the width is 50px!

https://codepen.io/abdisalan/full/NWqNYgJ

History

Of course you can't talk about the history of the web without mentioning Internet Explorer.

The border-box model, where the width and height include the padding + border, was used and popularized by Internet Explorer. In contrast, the world wide web consortium (W3C) have decided that the content-box model is the standard and is currently used and the default for most modern browsers.

Which do you prefer?

I personally prefer border-box, but they each have their pros and cons. Tell me which one you prefer on twitter!

border-box — You don't have to think about adding padding or border, they do the calculation for you and it's supported by older browsers.
content-box — Default behavior in most browsers. Lets you dictate the size of the content so it might be easier to fit other elements within.

If you want to include border-box in your next project, you can use this handy CSS rule to apply it to all your elements.

* {
    box-sizing: border-box;
}

Hope you enjoyed this post and you start using border-box in all your projects!

✌️