6 ms·
SELECT TransactionID, Amount, COUNT(*) FROM Transactions GROUP BY TransactionID, Amount I'll take 10% of savings
by madenine 7y ago
SELECT TransactionID, Amount, COUNT(*) FROM Transactions GROUP BY TransactionID, Amount
I'll take 10% of savings
- DoofusOfDeath 7y agoI would guess that "TransactionID" is where a lot of the challenge lies.
- ellisv 7y agoHAVING COUNT(*) > 2; I'll take 9% );
- mgraczyk 7y ago`>=` 8%?
- madenine 7y agoHAVING COUNT(*) >= 2; Back to 10%
- elhudy 7y agoAdd a date field or you might get legitimate duplicates if the payment amounts are the same across months.
- TheSoftwareGuy 7y agoThey shouldn't have the same transactionID then, no?
- elhudy 7y agoWouldn't think so. Each transaction ID would represent a unique transaction? >Other double payments made by mistake included times that the state received multiple invoices. I would assume each paid invoice was a new transaction ID. The real problem seems like there are two invoices being paid. Not that there are two transactions (which seems like just a symptom). It's possible that each invoice even has it's own ID.
- edwhitesell 7y agoAnd when there are two payments with different transaction ids from the gateway?
- madenine 7y agomost likely? my joke fails to pan out in reality
- JMTQp8lwXL 7y agoThis presumes all of the transaction data is centralized one place. 99% of the battle was probably getting that data aggregated, then performing the analytics, which is chalked up to this simple SELECT statement.
- slap_shot 7y agoThis is exactly the issue here. I worked as a contractor for the State Department for a couple of years on software that basically held the record of every dollar spent over the last 20 years. What seemed like a very small and simple problem initially revealed itself to be massive problem that even a team of 35 struggled to maintain. The purchase history for any given item spanned multiple systems with completely different topologies of data glued together by, literally, tens of thousands of lines of SQL. The project had been around for ~17 years when I worked on it and while data was landed in a final format where a query like the above could have been done, I wouldn't bet any serious money that the calculations were correct. Some five or six multi-million dollar rewrites had been attempted, but could never be done. Not to say Ohio's system is that this level, but I doubt there's a giant table sitting somewhere that such a simple query could be applied to. I like to think that somewhere in all that SQL was an Office Space-esque line of SQL syphoning off a few cents per transaction, but was never able to find one :)
- dang 7y ago"Please don't post shallow dismissals, especially of other people's work. A good critical comment teaches us something." "Don't be snarky." https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html