dump spend and advisor earnings

some_shit.sql

-- SPENT BY SEEKER
DROP TABLE IF EXISTS purchased20;
CREATE TEMPORARY TABLE purchased20 AS
SELECT t.user_id FROM bucket_transactions t
  INNER JOIN users u ON (u.id = t.user_id AND  u.user_type = 1)
  WHERE t.transaction_type IN (3, 4) AND
  t.created_at > '2009-09-16'
  GROUP BY t.user_id
  HAVING SUM(t.amount) > 20;

ALTER TABLE purchased20 ADD KEY(user_id);

SELECT t.user_id, SUM(t.amount) spent, IF(t.transaction_type=3, 'CALL_SERVICE_PURCHASED', 'MAIL_SERVICE_PURCHASED') purchased
  FROM purchased20
  INNER JOIN bucket_transactions t USING(user_id)
  WHERE t.transaction_type IN (3, 4) AND
  t.created_at >= '2009-09-16'
  GROUP BY t.user_id, t.transaction_type
  ORDER BY user_id
  INTO OUTFILE '/tmp/spent20.csv';

-- gzip /tmp/spent20.csv
-- exit from beta
-- scp root@beta:/tmp/spent20.csv.gz .


-- /////////////////////////////////////////////////////////////////////////////


-- EARNED BY ADVISOR
DROP TABLE IF EXISTS earned100;
CREATE TEMPORARY TABLE earned100 AS
SELECT t.user_id FROM bucket_transactions t
  INNER JOIN users u ON (u.id = t.user_id AND  u.user_type = 2)
  WHERE t.transaction_type IN (23, 24) AND
  t.created_at > '2009-09-16'
  GROUP BY t.user_id
  HAVING SUM(t.amount) > 100;

ALTER TABLE earned100 ADD KEY(user_id);

SELECT t.user_id, SUM(t.amount) earned, IF(t.transaction_type=23, 'CALL_SERVICE_SOLD', 'MAIL_SERVICE_SOLD') sold
  FROM earned100
  INNER JOIN bucket_transactions t USING(user_id)
  WHERE t.transaction_type IN (23, 24) AND
  t.created_at >= '2009-09-16'
  GROUP BY t.user_id, t.transaction_type
  ORDER BY user_id
  INTO OUTFILE '/tmp/earned100.csv';

-- gzip /tmp/earned100.csv
-- exit from beta
-- scp root@beta:/tmp/earned100.csv.gz .