FORUM SQL Habit Lesson 26 part 1 and part 2

Jakub

over 1 year

SOLVED

Lesson 26 part 1 and part 2

This might be a bit silly, I am new to SQL so pardon my simple queries. I was practicing calculating purchase rate in lesson 26 and I decided to create my own queries. The question my own query should answer is “What is the % of users who are trial users and signed up in the first 2 weeks of Bindle existance”.

So I wrote a query that calculates this (Query 1), and I wanted to check this with another query (Query 2) to see if the result is correct. I can’t figure out why I am missing 1% of the users when I run the 2nd query.

Query 1

Calcualate % of the users who are trial users and signed up in the first 2 weeks of Bindle existance. This query produces a result of 36%:

SELECT
  100 * COUNT(CASE WHEN status = 'trial' THEN id END) /
  COUNT(*) AS trial_users_first_2_weeks_Jan_2018
FROM users
WHERE
  signup_date BETWEEN '2018-01-01' AND '2018-01-14'

Query 2

Below query should return 64% as the query above returned 36% but it returns 63% and I don’t know why.

SELECT
  100 * COUNT(CASE WHEN status = 'customer' OR status = 'free' THEN id END) /
  COUNT(*) AS non_trial_users_first_2_weeks_Jan_2018
FROM users
WHERE
  signup_date BETWEEN '2018-01-01' AND '2018-01-14'

Query 3

Below query shows all unique values for status column in the user table for the same time frame as previous queries. I used it to figure out how to structure second query.

SELECT DISTINCT status
FROM users
WHERE 
  signup_date BETWEEN '2018-01-01' AND '2018-01-14'

Thank you.

REPLIES

Jakub

over 1 year Reply

The 1% discrepancy comes from the fact that the queries I wrote return integers. When the query is converted to float by replacing “100” with “100.0” everything adds up. Thanks Anatoli!

makaroni4

over 1 year Reply

Absolutely right! Here’s the solution breakdown.

Number types

By default, an SQL engine (the database that runs our queries) wants to keep the numeric type. For example, if you have multiple integers (no decimal point), it’ll give you an integer as a result.

What it really means is that for a database, numbers 100 and 100.0 are different – one has a decimal point (a “float” or “numeric” in PostgreSQL), and the other doesn’t have a decimal point (an “integer” number).

Here are a couple of queries you can actually run in the Playground to inspect these types:

SELECT pg_typeof(100.0)

SELECT pg_typeof(100)

Note that the pg_typeof() function exists only in PostgreSQL.

Choosing the number type for your query

There’re a couple of ways we can control the number type in a query’s result set.

Case 1: fall back to input types

If we know upfront that our input numbers are integers and we want an integer in return – nothing to do from our side, the database will take care of everything.

Here’s an example with integers:

SELECT 11 / 3
-- 3

and an example with decimal numbers:

SELECT 9.3 / 3
-- 3.1

Case 2: change integer to decimal

Sometimes (as in your example), we need to change the type. For example, we know that our input numbers are integers, and we want to have a decimal point in our result (like in the example with division ).

We have a couple of options. The easiest – we can multiply our operation by 1.0 (thus forcing the SQL engine to give us a decimal number in the result set)

SELECT 1.0 * 11 / 3
-- 3.6666666

or we can convert one number to a decimal:

SELECT 11::numeric / 3
-- 3.6666666

Case 3: change decimal to integer

Lastly, we need to report integer numbers, although we’re operating with decimal numbers. In other words, we need to round the result of our query:

SELECT ROUND(9.3 / 3)
-- 3

Note that the ROUND() function rounds the input to the closest integer:

SELECT ROUND(1.4 / 3) 
-- 0

SELECT ROUND(1.5 / 3) 
-- 1

If you want to force a specific rounding to the higher or lower integer, use the CEIL() and the FLOOR() functions:

SELECT CEIL(1.4 / 3) 
-- 1

SELECT FLOOR(1.5 / 3) 
-- 0