Skip to content

Instantly share code, notes, and snippets.

@brettjonesdev
Last active July 18, 2019 02:15
Show Gist options
  • Save brettjonesdev/21877be1fa1ca9d390bd1218986bd6ce to your computer and use it in GitHub Desktop.
Save brettjonesdev/21877be1fa1ca9d390bd1218986bd6ce to your computer and use it in GitHub Desktop.
CWA stuff
/* 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? */
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