Now that we are all sharing our Hive punks, I thought it would be interesting/fun to see what are those punks that have made it to profile pictures! So far there are only 71 in the list - looking forward to see it growing.
How to?
As always, sharing is caring. If you are into exploring @hivesql data, here is a query that will allow you to easily retrieve this table:
SELECT *,
CASE WHEN LEN(punksTable.[Punk image]) > 51 THEN SUBSTRING(punksTable.[Punk image], 45, 4)
ELSE SUBSTRING(punksTable.[Punk image], 45, 3) END as 'Punk number'
FROM( SELECT
'@' + [name] AS 'Account'
,JSON_VALUE(JSON_QUERY([posting_json_metadata], '$.profile'), '$.profile_image') AS 'Punk image'
FROM [DBHive].[dbo].[Accounts]
where posting_json_metadata like '%hivepunks%') punksTable