Last active
July 18, 2019 02:15
-
-
Save brettjonesdev/21877be1fa1ca9d390bd1218986bd6ce to your computer and use it in GitHub Desktop.
CWA stuff
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* Pledgeup.contact table: convert Unix timestamp to created_at timestamp, | |
select only rows that have expressed interest */ | |
WITH first_query AS | |
(SELECT FROM_UNIXTIME(property_pledgeup_interest/1000), '%Y-%m-%d %T UTC') | |
AND | |
SELECT * | |
FROM pledgeup.contact | |
WHERE property_pledgeup_interest IS NOT NULL), | |
/* For two Broadstripes tables: select only rows with tag = “pledgeup-interest”, | |
then join by id in other table (adding tag column), | |
then select only rows with tag, remove id column */ | |
second_query AS | |
(SELECT * | |
FROM broadstripes.conversation_tag | |
LEFT JOIN broadstripes.conversation | |
ON broadstripes.conversation_tag.conversation_id = broadstripes.conversation.id | |
WHERE tag = 'pledgeup-interest' | |
ALTER TABLE broadstripes.conversation DROP COLUMN id) | |
/* Stack Broadstripes and Pledgeup.contact tables together */ | |
SELECT * | |
FROM first_query | |
UNION | |
SELECT * | |
FROM second_query; | |
/* How to alias created_at date as “expressed_interest_at” in this script? */ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create table pledgeup; | |
use pledgeup; | |
create table contact ( | |
email varchar(50), | |
property_pledgeup_interest BIGINT | |
); | |
insert into contact (email, property_pledgeup_interest) values ('brett@shit.net', NULL); | |
insert into contact (email) values ('time@shit.net'); | |
insert into contact (email, property_pledgeup_interest) values ('brett@ballz.com', 1420998416685); | |
insert into contact (email, property_pledgeup_interest) values ('joy@hey.whatev', 1420999116685); | |
create table broadstripes; | |
use broadstripes; | |
create table conversation ( | |
id INT PRIMARY KEY AUTO_INCREMENT, | |
email varchar(50), | |
created_at TIMESTAMP DEFAULT NOW() | |
); | |
insert into conversation (email) values ('brett@shit.net'); | |
insert into conversation (email) values ('tim@poop.net'); | |
insert into conversation (email) values ('time@shit.net'); | |
create table conversation_tag ( | |
conversation_id INT, | |
tag varchar(50) | |
); | |
insert into conversation_tag (conversation_id, tag) values (1, 'horny-dads'); | |
insert into conversation_tag (conversation_id, tag) values (1, 'volunteer-interest'); | |
insert into conversation_tag (conversation_id, tag) values (2, 'pledgeup-interest'); | |
insert into conversation_tag (conversation_id, tag) values (3, 'pledgeup-interest'); | |
insert into conversation_tag (conversation_id, tag) values (1, 'horny-dads'); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment