select
   distinct
    stuff((
        select ',' + u.EmailID
        from tbl_registration u
        where u.Rid = Rid
        AND Interviewed=0
        order by u.Rid
        for xml path('')
    ),1,1,'') as userlist
from tbl_registration

group by EmailID

NTILE

NTILE

Distributes the rows in an ordered partition into a specified number of groups.

Syntax

NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )

integer_expression - Is number of groups desired.
partition_by_clause - Divides result set

Note : If number of partition is divisible by number of groups, rows will be evenly distributed.
Suppose, number of rows=53 and number of groups is 5, first 3 group will have 11 each and rest 2 will have 10 each.

Example

SELECT e.LastName, 
NTILE(4) OVER(ORDER BY SUM(SalesAmountQuota) DESC) AS Quartile, 
CONVERT (varchar(13), SUM(SalesAmountQuota), 1) AS SalesQuota 
FROM dbo.DimEmployee AS e 
INNER JOIN dbo.FactSalesQuota AS sq 
ON e.EmployeeKey = sq.EmployeeKey 
WHERE sq.CalendarYear = 2003 
AND SalesTerritoryKey IS NOT NULL 
AND SalesAmountQuota <> 0 
GROUP BY e.LastName 
ORDER BY Quartile, e.LastName;

Dense Rank & Rank

Dense Rank

Returns the rank of rows within the partition of a result set, without any gaps in the ranking.


Syntax

DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )

-- Azure SQL Data Warehouse and Parallel Data Warehouse

DENSE_RANK () OVER ( [ [ partition_by_clause ] order_by_clause ] )


Few important points

There can be more than one row with same rank.
Partition can be skipped, if we want Rank applied over whole result set.


Examples




SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
    ,DENSE_RANK() OVER 
    (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank
FROM Production.ProductInventory AS i 
INNER JOIN Production.Product AS p 
    ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4
ORDER BY i.LocationID;
GO

Ranking all rows in a result set
SELECT TOP(10) BusinessEntityID, Rate, 
       DENSE_RANK() OVER (ORDER BY Rate DESC) AS RankBySalary
FROM HumanResources.EmployeePayHistory;

Rank


RANK gives you the ranking within your ordered partition. Ties are assigned the same rank, with the next ranking(s) skipped. So, if you have 3 items at rank 2, the next rank listed would be ranked 5.

DENSE_RANK again gives you the ranking within your ordered partition, but the ranks are consecutive. No ranks are skipped if there are ranks with multiple items.



* Just replace Rank to Dense_Rank and vice-versa.

Design Pattern

Design Pattern

Design Patterns provide standardized and efficient solutions to software design and programming problems that are re-usable in your code. Software Architects and developers use them to build high quality robust applications.

3 categories of design Patterns

1. Creational
2. Structural
3. Behavioural

Oracle Cloud Part I

What is cloud

Using a network or a server hosted on internet to store, manage and process data, rather than on a local system or a personal computer, it's also termed as On demand computing, Pay as you use service etc.

So cloud is nothing but a virtual space provided via internet.

How much does it cost

** there are some free cloud service providers letting out small space. In this blog let's bring focus back to Oracle Cloud.

Generally cloud usage can be as below

Pay as much you use

Enter into a contract for specified amount of space, once you run out of space upgrade for new package.

Since this is commercial, you may find out more attractive pricing 

Oracle Cloud