Average Time Difference Between Rows

December 25, 2020
Coding Bits

Suppose you had sales table with timestamp and customer_id as the columns.

id timestamp customer_id
1 2011-03-17 10:31:01 7
2 2011-03-17 10:45:05 8
3 2011-03-17 10:47:05 7
4 2011-03-17 10:50:10 8

You want to query for what’s the average time between purchases for each customer.

You can use:

SELECT t.customer_id,
       AVG(t.difference) as interval
FROM ( 
  SELECT customer_id,
         timestamp - lag(timestamp) OVER (PARTITION BY customer_id ORDER BY timestamp) AS difference
  FROM sales
) t
GROUP BY t.customer_id

You’ll get:

customer_id interval
7 00:16:04
8 00:05:05

Sources:

How to Make Fish Keycaps

November 30, 2020
Crafty Curios

Here are the steps I use to cast a fish keycap. If you’re completely new, see the Z-Butt wiki and my older blog post.

petri fish at an angle

First I cast a bottom piece. Thanks to the Z-Butt community, I learned to get the petri effect, you need to wait for about 1-2 hours before adding in alcohol ink to the resin. Here I waited for 1.5 hours.

alcohol ink

After the bottom piece is cured, I add a polymer clay fish. I use acrylic paint to add details and dab a dollop of resin to secure the fish to the base. When everything looks dry, I invert it to cast the top piece.

WIP keycap

That’s it!

Unicone Art vs Dr Crafty

November 14, 2020
Crafty Curios

After casting keycaps over a couple of months, I’ve finally finished my first bottle of resin. Now I’m trying out a different brand and realize not all epoxy resins have the same properties. I started with Unicone Art a couple of months ago and now am using Dr Crafty.

Here’s a comparison of the resin brands Unicone Art (left) and Dr Crafty (right):

resin compare

Epoxy Resin Brand Unicone Art Dr Crafty
Cure Time 18 hours – hard enough to work with, 24+ hours – full hardness 24 hours – still very pliable, 30 hours – hard enough to work with
Casting Great for casting More viscous and will not conform to certain complex molds
Clarity Clear but small trapped air bubbles will make the keycaps appear cloudy Crystal clear and fewer air bubbles
Odor I couldn’t detect anything Strong chemical smell from the hardener

If you look closely at the photo above (and ignore the different dye colors), there are a lot more tiny air bubbles in the left keycap than the right. I let all resin degas before casting, but it seems like for Unicone Art, the air bubbles have a harder time making their way out.

I prefer strongly Unicone Art for its casting property and turnaround time. However, Dr Crafty is visibly clearer.

Maybe I’ll try to cast the bottom pieces with Unicone Art and the clear top with Dr Crafty. Will report back.

Stars Over Lakes

November 14, 2020
Crafty Curios

star keys

These will be the final star keycaps I cast. I’m pretty satisfied. Aside from different colors, there’s not much left to explore.

lake keys

I might try to make a few lakes keycaps. I’m not loving the overall key design ’cause it leads to large air bubbles trapped everywhere.