Weekend Worked Hour in MySQL

SET NOCOUNT ON;

WITH hours_worked as (

  

SELECT

      emp_id,

         CASE 

       WHEN datepart(minute,TIMESTAMP) >= datepart(minute,lag(TIMESTAMP) OVER(PARTITION BY CAST(TIMESTAMP AS date),emp_id ORDER BY TIMESTAMP)) then datepart(hour,timestamp) - datepart(hour,lag(TIMESTAMP) OVER(PARTITION BY CAST(TIMESTAMP AS date),emp_id ORDER BY TIMESTAMP)) 

         ELSE datepart(hour,timestamp) - datepart(hour,lag(TIMESTAMP) OVER(PARTITION BY CAST(TIMESTAMP AS date),emp_id ORDER BY TIMESTAMP)) - 1

       END AS hours_worked

FROM   attendance

-- only weekends

WHERE  datepart(weekday,TIMESTAMP) IN(7,1)

)

SELECT

   emp_id,

      SUM(hours_worked) as hours_worked

FROM  hours_worked

GROUP BY emp_id

ORDER BY hours_worked desc

go

 

Crypto Market Algorithm Report in MYSQL

 

select c.algorithm, q1.tot, q2.tot, q3.tot, q4.tot

from coins c

join (select sum(volume) as tot,coins.algorithm from transactions

left join coins on transactions.coin_code = coins.code

where month(dt) between '01' and '03' and year(dt)=2020

group by coins.algorithm) q1 on c.algorithm=q1.algorithm

join (select sum(volume) as tot,coins.algorithm from transactions

left join coins on transactions.coin_code = coins.code

where month(dt) between '04' and '06' and year(dt)=2020

group by coins.algorithm) q2 on c.algorithm=q2.algorithm

join (select sum(volume) as tot,coins.algorithm from transactions

left join coins on transactions.coin_code = coins.code

where month(dt) between '07' and '09' and year(dt)=2020

group by coins.algorithm) q3 on c.algorithm=q3.algorithm

join (select sum(volume) as tot,coins.algorithm from transactions

left join coins on transactions.coin_code = coins.code

where month(dt) between '10' and '12' and year(dt)=2020

group by coins.algorithm) q4 on c.algorithm=q4.algorithm

GROUP BY c.algorithm

ORDER by c.algorithm asc;

Customer Spending in MySQL

 

SELECT c.customer_name, ROUND(SUM(i.total_price), 6)

FROM customer c

INNER JOIN invoice i ON c.id=i.customer_id

GROUP BY c.customer_name

HAVING SUM(i.total_price)<0.25*(SELECT AVG(total_price) FROM invoice)

ORDER BY ROUND(SUM(i.total_price), 6) DESC

Product Sales Per City in MySQL

 

select ci.city_name, pr.product_name, ROUND(sum(ii.line_total_price), 2) as tot from city ci, customer cu, invoice i, invoice_item ii, product pr where ci.id = cu.city_id and cu.id = i.customer_id and i.id = ii.invoice_id and ii.product_id = pr.idgroup by ci.city_name, pr.product_name order by tot desc, ci.city_name, pr.product_name

Bitwise AND in Python

 

import math

import os

import random

import re

import sys

from collections import defaultdict

#

# Complete the 'countPairs' function below.

#

# The function is expected to return a LONG_INTEGER.

# The function accepts INTEGER_ARRAY arr as parameter.

#

def countPairs(arr):

    po2 = lambda x: x > 0 and not (x & (x - 1))

    d = defaultdict(int)

    for x in arr:

        d[x] += 1

    d = list(d.items())

    ans = 0

    for i in range(len(d)):

        a, a_cnt = d[i]

        for j in range(i, len(d)):

            b, b_cnt = d[j]

            if po2(a & b):

                if a == b:

                    ans += (a_cnt * (a_cnt - 1)) // 2

                else:

                    ans += a_cnt * b_cnt

    return ans          

if __name__ == '__main__':

    fptr = open(os.environ['OUTPUT_PATH'], 'w')

    arr_count = int(input().strip())

    arr = []

    for _ in range(arr_count):

        arr_item = int(input().strip())

        arr.append(arr_item)

    result = countPairs(arr)

    fptr.write(str(result) + '\n')

    fptr.close()

Task of Pairing

 

#!/bin/python3 import math import os import random import re import sys # # Complete the 'taskOfPairing' function below. # # The function is expected to return a LONG_INTEGER. # The function accepts LONG_INTEGER_ARRAY freq as parameter. # def taskOfPairing(freq):     # Initialize the start of the continuous subarray     i_0 = 0     # Initialize the end of the continuous subarray     i_1 = 1     n = len(freq)     total = 0     while i_1 < n:         # While not at the end of the subarray, check if the next value is non-zero         if freq[i_1] == 0:             # If value is zero, take the sum of the continuous subarray and integer divide by 2. That's the             # number of pairs in this segment             total += sum(freq[i_0: i_1]) // 2             # The new start of the continuous array is here             i_0 = i_1         i_1 += 1     # Upon reaching the end, find the number of pairs of the last subarray     total += sum(freq[i_0:i_1]) // 2     return total                   if __name__ == '__main__':     fptr = open(os.environ['OUTPUT_PATH'], 'w')     freq_count = int(input().strip())     freq = []     for _ in range(freq_count):         freq_item = int(input().strip())         freq.append(freq_item)     result = taskOfPairing(freq)     fptr.write(str(result) + '\n')     fptr.close()

Longest Subarray in python

 

#!/bin/python3
	import math
	import os
	import random
	import re
	import sys
	#
	# Complete the 'longestSubarray' function below.
	#
	# The function is expected to return an INTEGER.
	# The function accepts INTEGER_ARRAY arr as parameter.
	#
	def longestSubarray(arr):
	n = len(arr)
	ans = 0
	# O(n^2) is okay because of constraints.
	for i in range(n):
	w = []
	cnt = 0
	for j in range(i, n):
	if arr[j] in w:
	cnt += 1
	continue
	if len(w) == 0:
	w.append(arr[j])
	elif len(w) == 1:
	if abs(w[0] - arr[j]) > 1:
	break
	else:
	w.append(arr[j])
	else:
	break
	cnt += 1
	ans = max(ans, cnt)
	return ans
	if __name__ == '__main__':
	fptr = open(os.environ['OUTPUT_PATH'], 'w')
	arr_count = int(input().strip())
	arr = []
	for _ in range(arr_count):
	arr_item = int(input().strip())
	arr.append(arr_item)
	result = longestSubarray(arr)
	fptr.write(str(result) + '\n')
	fptr.close()