codecademy-SQL-challenge-RPA-Customer-Seg.sqlite

-- 1
-- What are the column names?
SELECT *
FROM users
LIMIT 20;
 
-- 2
-- Find the email addresses and birthdays of users whose 
-- birthday is between 1980-01-01 and 1989-12-31.
SELECT email, birthday
FROM users
WHERE birthday BETWEEN '1980-01-01' AND '1989-12-31';
   
-- 3
-- Find the emails and creation date of users 
-- whose created_at date matches this condition.
SELECT email, created_at
FROM users
WHERE created_at < '2017-05-01';

-- 4
-- Find the emails of the users who received the ‘bears’ test.
SELECT email, test
FROM users
WHERE test = 'bears';

-- 5
-- Find all the emails of all users who 
-- received a campaign on website BBB.
SELECT email, campaign
FROM users
WHERE campaign LIKE 'BBB%';

-- 6
-- Find all the emails of all users who received ad copy 2 in 
-- their campaign.
SELECT email, campaign
FROM users
WHERE campaign LIKE '%2';

-- 7
-- Find the emails for all users who received both a campaign and a test. 
-- These users will have non-empty entries in the 
-- campaign and test columns.

SELECT email, campaign, test
FROM users
WHERE campaign IS NOT NULL
  AND test IS NOT NULL;

-- 8
-- Challenge
-- One of the members of the marketing team had an idea of calculating
-- how old users were when they signed up.
-- The top two solutions as far as simple coding and possible right answers. 

SELECT email, birthday, 
  (created_at - birthday) AS age
FROM users
LIMIT 10;

SELECT email, birthday, created_at,
  CAST ((JULIANDAY(created_at) - JULIANDAY(birthday)) / 365 AS int) AS age
FROM users
LIMIT 10;

-- SELECT email, cast((julianday(created_at) - julianday(birthday)) / 365 as int) as registered_age
-- FROM users
-- LIMIT 10;

-- SELECT CAST(julianday(created_at) - julianday(birthday) as integer) / 365 AS Age
-- FROM users
-- LIMIT 10;

-- SELECT email, created_at, birthday,
--   CASE
--     WHEN created_at IS NOT NULL
--       AND birthday IS NOT NULL
--     THEN (created_at - birthday)
--   END AS age
-- FROM users
-- LIMIT 10;

-- ALTER TABLE USERS
-- ADD age INT;
-- UPDATE USERS
-- SET AGE = CREATED_AT - BIRTHDAY
-- WHERE AGE IS NULL;

-- SELECT email, (created_at) - (birthday) AS age
-- FROM users
-- LIMIT 10;