User Info Notes

## User Info Notes

-- free version of mode does not allow for editing data tables

-- example below of how to select users created after a certain date
SELECT
	id
FROM users
	WHERE
	 created_at <= '{{ds}}'


-- our task is to create a user info table, with relevant information

SELECT
	id  									AS user_id,
	IF(users.created_at = {{ds}}, 1, 0) 	AS created_today,
	IF(users.deleted_at <= {{ds}}, 1, 0) 	AS is_deleted,
	IF(users.deleted_at = {{ds}}, 1, 0)		AS is_deleted_today,
	IF(users_with_orders.user_id IS NOT NULL, 1, 0)	AS has_ever_ordered,
	IF(users_with_orders_today.user_id IS NOT NULL, 1, 0)	AS ordered_today,
	'{{ds}}'
FROM users
LEFT OUTER JOIN
	(
	SELECT
		DISTINCT user_id
	FROM
		orders
	WHERE
		created_at <= '{{ds}}'
		) users_with_orders
	ON
		users_with_orders.user_id = users.id