Defenestrategy fucked around with this message at 01:35 on Feb 3, 2023

classes
	pid, (unique) classid
class_times
	pid, (incrementing) timeslotid, (foreign key) classes.classid, date_reference, timeslot_reference, etc
members
	pid, (unique) memberid, fullname
member_registrations
	pid, (incrementing) registrationid, (foreign key) members.memberid, (foreign key) classes.classid
member_attendance
	pid, (foreign key) member.memberid, (foreign key) class_times.timeslotid, attendance_enum    (plus add a unique constraint on memberid and timeslotid)

Ranzear fucked around with this message at 05:21 on Feb 3, 2023

Just-In-Timeberlake fucked around with this message at 14:30 on Feb 3, 2023

SELECT
  p.person_id,
  (SELECT COALESCE(COUNT(person_id), 0) FROM notes n WHERE p.person_id = n.person_id) AS note_count,
  (SELECT COALESCE(COUNT(person_id), 0) FROM codes c WHERE p.person_id = c.person_id) AS code_count
FROM person p
GROUP BY p.person_id;

person_id	notes	codes
1		2	1
2		0	1
3		0	0

SELECT
  p.person_id,
  COALESCE(COUNT(person_id), 0) AS note_count
FROM person p
LEFT JOIN notes n ON p.person_id = n.person_id
GROUP BY p.person_id;

Ranzear fucked around with this message at 04:17 on Feb 4, 2023

Ranzear fucked around with this message at 04:24 on Feb 4, 2023

TheFluff fucked around with this message at 15:41 on Feb 4, 2023

Ranzear fucked around with this message at 01:48 on Feb 5, 2023

RandomBlue fucked around with this message at 05:06 on Feb 5, 2023

double nine fucked around with this message at 20:12 on Feb 16, 2023

SELECT
    WantedTable.*
FROM
    (SELECT * FROM Last12MonthsTable UNION ALL SELECT * FROM OlderTable) AS WantedTable
    LEFT JOIN Last12MonthsTable AS Check1 ON
        Check1.CustomerID = WantedTable.CustomerID AND
        Check1.ProductID = 987654
    LEFT JOIN OlderTable AS Check2 ON
        Check2.CustomerID = WantedTable.CustomerID AND
        Check2.ProductID = 987654
WHERE
    Check1.CustomerID IS NULL AND
    Check2.CustomerID IS NULL AND
    WantedTable.ProductID = 123456 AND
    WantedTable.PurchaseDate >= '20220101'

Hammerite fucked around with this message at 20:56 on Feb 16, 2023

DECLARE @pId1				INT = 123456
	, @pId2				INT = 987654
	, @customerId			INT = 1
	, @dateCutoff			DATETIME = '1/1/2022';

;WITH cte AS (
	SELECT	[productId],
            	[customerId],
            	[purchaseDate],
            	[storeId],
            	[invoiceNumber]
	FROM	@salesNew
	WHERE	[customerId] = @customerId
		AND [productId] = @pId1
		AND [purchaseDate] >= @dateCutoff
	UNION
	SELECT	[productId],
            	[customerId],
           	[purchaseDate],
           	[storeId],
            	[invoiceNumber]
	FROM	@salesOld
	WHERE	[customerId] = @customerId
		AND [productId] = @pId1
		AND [purchaseDate] >= @dateCutoff
)
SELECT	[productId],
        [customerId],
        [purchaseDate],
        [storeId],
        [invoiceNumber]
FROM	[cte]
WHERE	(
			SELECT	COUNT(*)
			FROM	(
						SELECT	[productId],
							[customerId],
							[purchaseDate],
							[storeId],
							[invoiceNumber]
						FROM	@salesNew
						WHERE	[customerId] = @customerId
							AND [productId] = @pId2
						UNION
						SELECT	[productId],
							[customerId],
							[purchaseDate],
							[storeId],
							[invoiceNumber]
						FROM	@salesOld
						WHERE	[customerId] = @customerId
							AND [productId] = @pId2
					) AS [t]
		) = 0

Just-In-Timeberlake fucked around with this message at 21:07 on Feb 16, 2023

SELECT *
FROM purchases_last_12_months
WHERE ProductID = '123456'
AND PurchaseDate >= '20220101'
AND CustomerID NOT IN (
    -- ignore customers that have purchased product B in the last 12 months
    SELECT CustomerID
    FROM purchases_last_12_months
    WHERE ProductID = '987654'
)
UNION
SELECT *
FROM older_purchases
WHERE ProductID = '123456'
AND PurchaseDate >= '20220101'
AND CustomerID NOT IN (
    -- ignore customers that have purchased product B *prior* to the last 12 months
    SELECT CustomerID
    FROM older_purchases
    WHERE ProductID = '987654'
)

/*
probably not performant on large datasets:

with cte purchases for all cols from (purchases_last_12_months unioned with older_purchases) where ProductID = '123456' AND PurchaseDate >= '20220101'
then with cte customers_to_ignore as CustomerID from (purchases_last_12_months unioned with older_purchases) where ProductID = '987654'
get all data from purchases where CustomerID not in customers_to_ignore 
*/

mortarr fucked around with this message at 22:29 on Feb 16, 2023

with all_purchases as (
  select * from purchases
  union all
  select * from purchases_old
)
select *
from all_purchases
where
    all_purchases.product_id = 123456
    and all_purchases.purchased_at >= '2022-01-01'::timestamptz
    -- exclude rows where a purchase of product 987654 exists for this customer
    and not exists(
      select 1
      from all_purchases as all_purchases_inner
      where
      	all_purchases_inner.customer_id = all_purchases.customer_id
      	and all_purchases_inner.product_id = 987654
    )

TheFluff fucked around with this message at 03:15 on Feb 17, 2023